When you change servers and restore msdb, you may find your maintenance plans failing and putting the following messages into various logs:
The SSIS subsystem failed to load Subsystem could not be loaded The job has been suspended The specified module could not be found
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,
SELECT * FROM msdb.dbo.syssubsystems
If the directory pointed to by the rows doesn’t exist, you’ve found the problem! Two quick queries will fix you right up:
use msdb go delete from msdb.dbo.syssubsystems exec msdb.dbo.sp_verify_subsystems 1 go
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.