Or, "Further Lessons Learned from Today's Migration."

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:

  -- Delete the existing rows.
 DELETE FROM msdb.dbo.syssubsystems
 -- Fill the table with new rows pointing to the proper location of the DLLs.
 EXEC msdb.dbo.sp_verify_subsystems 1

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.

Labels: SQL Server

This entry was posted on Saturday, February 23, 2008 at 1:35 PM.