DBA Services Australia News

Follow us on Google+, Facebook or Twitter to keep up with the latest news direct from our Blog!
Sia Shamsai

The SSIS Subsystem Failed to Load

When you change servers and restore msdb, you may find your maintenance plans failing and putting the following messages into various logs:

The problem is most likely that the location of your SQL Server installation directory differs from that of the old server. Ours happened to be on a different disk drive. SQL Server keeps a table of pointers to the SSIS DLLs in msdb, and you need to flush this table and repopulate it with the new locations (KB article 914171). This is very simple.

To verify that this is in fact the problem,

If the directory pointed to by the rows doesn’t exist, you’ve found the problem! Two quick queries will fix you right up:

And finally, you need to restart SQL Server Agent for it to pick up the new table entries and unsuspend the SSIS jobs.

One More Thing!

If you’ve changed servers and have SSIS packages (like maintenance plans), there’s another thing you most likely need to do. Each package/plan has at least one database connection, and they’re probably pointing to the old server. You need to modify each package/plan individually, changing the existing connection if you can (I couldn’t — all of the input boxes were disabled) or creating a new connection and changing all of the steps to use it. I’ve never been a user of DTS packages — too rickety and GUI-driven for me — but I can see why no one likes the direction Microsoft went with turning maintenance plans into DTS packages in SQL Server 2005.

Thanks to Funky Glass Art and SQL Server Musings for the original post for this fix.

SQL Server Support Team

Fully certified Microsoft SQL Server Database Administrators offering Remote DBA & SQL Server infrastructure support services.

Leave a Reply


Request a Quote

Captcha content below