I have a bunch of maintenance plans that I want to copy/migrate to sql server 2016. I am able to connect to Integration Services on sql server 2016 but I get the following error when expanding 'MSDB' folder under 'Stored Packages' folder in Integration Services:
The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 R2 Books Online.
Login failed for user 'X'. (MsDtsSrvr)
I assume the configuration is ok since the plans have been there for years now. Is there other ways to deal with maintenance plans without having integration services involved?
Best Answer
It might not be the exact answer you're looking for, but consider using jobs instead of maintenance plans. Ola hallengren has a full (free) maintenance solution that you can download and deploy on your server. ola.hallengren.com It's used by a lot of DBAs, and has been around for years.
All you have to do is deploy the scripts and add some schedules, you can also fine tune everything to fit your needs. There's code examples and options on the Ola site.
A big benefit you get from this, is that you can simply script out everything, and deploy it on any server you want, without much hassle.
It might be worth your time checking this out.