I was tasked with setting up a maintenance plan for two databases we deployed to a customer recently. Since the databases are small I figured it would be quick and easy to use the Maintenance Plan wizard. After following all the prompts when it came time to create the plan I received the following error from SQL Server.
“Create maintenance plan failed the specific ‘@subsystem’ is invalid”
There was some additional information as well, but the main error gave me pause. Instead of quickly going to Google to investigate the issue I decided to try and create the maintenance plan manually. Well, as you can guess I got the same error message.
I then Googled the error and found this:
Microsoft advises the problem is SQL Server Integration Services needs to be installed. http://support.microsoft.com/kb/909036
So, I notified our customer SSIS needs to be installed. I did some additional investigation and found some information about service packs fixing this issue. I then determined the service pack level on the server. It seems it is an enterprise edition with SP2 installed. Since there have been numerous updates and a SP3 released I advised the customer of this.
The database server by default installs a Business Intelligence Studio, a scaled down version of Visual Studio. I was not able to find any additional information as to why, if this is installed was still getting the error. It is not SSIS I know but if BI Studio is installed I would think the needed components to create and launch maintenance plans would be there as well. Wrong.
I then attempted to create a work around using the BI Studio. I created a project and all the steps I wanted within the maintenance plan. Saved all the new steps. Launched the project to test it and it ran fine. No errors. Very encouraging. I then deployed the package to the SQL Server and ran into the next problem. Since SSIS is not installed I can not view the package I deployed to the server.
After creating the second plan the same way and deploying the package to the server. I knew the location where the package was deployed so I attenpted to create a Job to run the package. After going through all the steps to set up the Job and schedule it, I ran into the first error again.
“Create maintenance plan failed the specific ‘@subsystem’ is invalid…”
I documented all of this information and sent an email to my chain of command and the customer. I am still waiting on word back as to what they want me to do next.
Since I created the packages in the BI studio I can log in and run the programs and do the backups manually. But we all know this is NOT a viable option, short or long term.
I’ll update this post when I have solved the problem.