Tuesday, February 8, 2011

SSIS subsystem failed to load. The job has been suspended.

I came across a new one a month ago that I hadn’t seen before. I got a complaint from a developer that a job was hanging on one of our development servers. A consultant had set the server up before my time here, so I really didn’t know much about the install or configuration on this particular server.The error in the job history looked like this:


Step 1 of job '' (0x1077ED50A744154FFEEBC695E3C87432) cannot be run because the SSIS subsystem failed to load. The job has been suspended.


Additionally, the job had been suspended and we were unable to run it again. If we drop and recreate it, it would run, fail and suspend all over again.
Well I hadn't seen that one before so I did some high tech DBA debugging (i.e. google search).


The good news was that I got search results. The first link that I chased down was a kb article and it was regarding a similar error when you restore msdb on 2005. Well this was a 2008 R2 box that I had recently upgraded from 2008, but after some more digging I figured out that indeed the consultant who had built the instance originally had tried to duplicate another server by simply restoring msdb from that server. The key is the msdb.dbo.syssubsystems table and the msdb.dbo.sp_verify_subsystems undocumented stored procedure.


Looking at the syssubsystems table I verified that the paths listed for subsystem_dll did not exist on the server. However they did match the install path layout of another server.


To check the current file paths that SQL believes exist


select * from msdb.dbo.syssubsystems































subsystem subsystem_dllagent_exe
TSQL [Internal] [Internal]
ActiveScripting C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLATXSS.DLL NULL
CmdExec C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLCMDSS.DLL NULL
Snapshot C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.DLL C:\Program Files\Microsoft SQL Server\100\COM\SNAPSHOT.EXE
LogReader C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.DLL C:\Program Files\Microsoft SQL Server\100\COM\logread.exe
Distribution C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.DLL C:\Program Files\Microsoft SQL Server\100\COM\DISTRIB.EXE
Merge C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.DLL C:\Program Files\Microsoft SQL Server\100\COM\REPLMERG.EXE
QueueReader C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLREPSS.dll C:\Program Files\Microsoft SQL Server\100\COM\qrdrsvc.exe
ANALYSISQUERY C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLOLAPSS.DLL NULL
ANALYSISCOMMAND C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLOLAPSS.DLL NULL
SSIS C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLDTSSS.DLL C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
PowerShell C:\Program Files\Microsoft SQL Server\MSSQL10.QA1\MSSQL\binn\SQLPOWERSHELLSS.DLL C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe













The Fix:






use master
GO
--ALLOW updates
EXEC sp_configure 'allow updates', 1;
reconfigure with override;
GO
--select * from dbo.syssubsystems --(verify old values)
--DELETE syssubsystems
use msdb
GO
delete from msdb.dbo.syssubsystems
GO

--REPOPULATE syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
GO
--select * from dbo.syssubsystems --(verify new values)
--DISABLE updates
use master
GO
EXEC sp_configure 'allow updates', 0;
reconfigure with override;
EXEC sp_configure;
















Links:


10 comments:

  1. Hi,
    I my 'subsystem' table, a row for 'SSIS' is absent. When I add it manually in table and restart SQL agent service, it disappears and the error persists. What can be the problem? Can you please help? This problem started when I added an instance on a dev server.

    ReplyDelete
  2. Have you verified the install path for SSIS? If so what version/edition of SQL?

    ReplyDelete
  3. It is SQL 2008 R2 Enterprise edition. Yes I verified path, and it is correct.
    Can it be related to SQL server service account of default instance? As I mentioned, this problem started when I added an instance to previously installed SQL server. Services for both instances are running under different service accounts.

    ReplyDelete
  4. It might be, do the service accounts have permissions to the path(s) in question?

    ReplyDelete
  5. Edq,
    I'll check and get back to you on Monday. I'll try out the changes tomorrow (i.e.Saturday);will make all SQL services run under 'Network Service' and then check.
    Anyway, Thanks so much for all your help!-Nishad

    ReplyDelete
  6. Thanks Edq!
    I'll change all SQL services to run under 'Network Service' and check. I'll do this change tomorrow and inform you the outcome.
    Thanks anyway for all your help!
    -Nishad

    ReplyDelete
  7. Hi EDQ,
    Now all the SQL services are running under 'NetworkService'.But still I am facing problem with maintenance plans and jobs. I get following error-
    Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c0010014. (Microsoft.SqlServer.ManagedDTS)
    -------------
    In 'subsystem' table, a row for 'SSIS' is absent. When I add it manually in table and restart SQL agent service, it disappears. I tried registering DLLs as suggested in some forums on net, but no luck.
    Thanks,
    Nishad

    ReplyDelete
  8. Nishad,
    So when you say added manually did you do delete the rows and then use
    exec msdb.dbo.sp_verify_subsystems 1
    to repopulate? If not I would try that otherwise at this point I would suggest maybe re-installing SSIS. You could also try one of the forums like stackoverflow.com or even msdn.

    ReplyDelete
  9. Edq, Thanks!
    I guess I have now tried all the possible solutions,other than re-installing SQL Server :) But problem persists. Many users have posted this issue on MS Connect site- https://connect.microsoft.com/SQLServer/feedback/details/557402/ssms-can-no-longer-create-or-edit-job-steps#tabs

    Thanks for all your help!
    Nishad.

    ReplyDelete
  10. Hi Edq,
    This issue is finally resolved. I disabled UAC and everything is back to normal now.
    I think SSIS DLLs were not accessible internally, because UAC was enabled. After disabling UAC, I can now create jobs and maintenance plans on all instances.
    Thanks for your help!
    -Nishad

    ReplyDelete