Every once in awhile an engineer will have this error pop up for one of their systems:
If the engineer is a SQL DBA than there is no problem as they will understand both the source and ultimately how to fix the problem.
Sadly not everyone who has or manages a SQL server is a DBA. There are plenty of cases where a sysadmin acquires a few SQL servers which they know the basics of managing or at least how to point an app server at to use it, but they may never have had the time to dig deeper into SQL, thus MsDtsServer100 IS Package Failed is not always particularly useful.
The Alert Description offers a useful clue “Maintenance Plan” Failed (Though this title will vary based on if the default plan name is used)
So how do you troubleshoot this error?
If you remote the SQL server referenced in the error you can launch SQL Management Studio and connect to the instance in question. If you expand the Management folder you will find a folder called Maintenance Plans
In this case the Maintenance Plan has been renamed to “Nightly Backups”
If you right click and select view history for the Maintenance Plan you will be presented with the following:
This is where things get confusing, everything looks like it ran perfectly as per the little green check marks of success. You see a Rebuild Indexes, a History Cleanup, some generic Maintenance task, and a DB Backup. All successful.
So where is the error coming from?
If you navigate to the Application Event Log on the SQL server for the time the alert was generated you will find the answer:
Subplan II actually had two components: one was a rebuild indexes which you can see from the SQL Management Studio history occurred successfully. The other item in this particular case was a reorganize indexes which was failing. Reorganizing indexes immediately after rebuilding them doesn’t sound like a very good order of operations. For this specific issue I recommended that the engineer remove the reorganize indexes from subplan II and the error has never happened since. So if you see MsDtsServer 100 IS Package Failed you are going to want to go to the Application Event Log of the SQL Server to figure out the source of the problem.