READ COMPLETELY BEFORE YOU DO ANYTHING
Here was the error I received when running a maintenance plan
Request to run job XYZ (<<trimmed>>) refused because the job has been suspended.
I checked in the ~/MSSQL/Logs/SQLAGENT.OUT
And I get:
Subsystem 'SSIS' could not be loaded (reason: Access is denied)
AHA, now to troubleshoot that
I opened up procmon, and started a watch excluding Operations containing Reg, and only including path containing my MSSQL folders…Set autoscroll, and restarted SQL Agent…
I found: ~ MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLDTSSS.DLL QueryOpen SUCCESS
Odd, that meant it’s not an NT permission error (to me)
But I still got “Subsystem 'SSIS' could not be loaded (reason: Access is denied)” in the SQLAGENT.LOG folder
humm… Reading more in the procmon output..
I find a “FILE LOCKED WITH ONLY READERS” on MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Binn\SQLDTSSS.DLL
Maybe this is it??? Google finds nothing (Great) and to humor myself, neither does Bing find anything (that was expected)
But some more digging, finds this is irrelevant and I am barking up the wrong tree on that
humm… Reading more in the procmon output.. nothing
Decided to limit my process monitor output a bit more, and only included SQLDTSSS.DLL in path
Everything was success (or locked with read) when I restarted SQL Agent… so the problem isn’t really loading the DLL I suspect, but rather some path that DTS is trying to use, so I started digging and testing a few things
AHA, I FOUND IT !!!!!!!!!!!!!!
Ok, so on this particular machine (due to disk space constraints) we installed the Program files on the D Drive. The problem was that the \Program Files\Microsoft SQL Server\100\DTS\Binn directory did not have execute permissions to the user that SQL agent is running as (which is Probably actually a group: SQLServerMSSQLUser$MACHINENAME$MSSQLSERVER).. I granted exec to that folder to that group (or user if you are running as a user instead) and VOILA, restarting SQL Agent no longer gave me the error, and Jobs would run as desired. YAHOO
Case closed.
**Note: I got this in SQL 2008 Web Edition, but I suspect it applies to any SQL version, Use at your own risk **
Reference for SQL2005/2008, for another possible reason why your SSIS subsystem isn’t loading, it may be a mismatch in your msdb.. possibly from you restoring a msdb from one machine to another?!?! Just need to reset all the DLL’s so they reference the right location, here’s a link on how: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914171