Post reply

The SSIS subsystem failed

  • February 13, 2012 at 1:21 pm

    #251544

    Hi,

    I am getting below listed error when i run SSIS Package using SQL JOB.

    Unable to start execution of step 2 (reason: The SSIS subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed

    so checked on this server MSDB database, syssubsystems table and found there is one entry missing on this server for SSIS Subsystem.

    select * from sysSubsystems

    got only 10 rows.

    I went ahead and insert this value manually in to this table as well, but as soon as I restart my SQL Agent this entry is missing again from the MSDB database.

    Also i am still getting same error.

    While serching on google found this article and i did follow up on this but no luck so far.

    http://support.microsoft.com/?kbid=914171

    Thanks in advance for your input.

  • February 13, 2012 at 1:34 pm

    #1446482

    Why do you think 1 row is missing and why did you add it manually? What type of package are you trying to run (was it created as a maintenance plan, using SSIS, etc.?)?

    Jared
    CE - Microsoft

  • February 13, 2012 at 1:36 pm

    #1446484

    Also, is SSIS installed properly? Is SSIS running? What account is it running under?

    Jared
    CE - Microsoft

  • February 13, 2012 at 1:49 pm

    #1446496

    when i checked in all other server i found that Row in system database.

    so i did insert that row manually but whenever i restart my SQL Agent it revert back those changes..

    I am runninng SSIS Package not Maintenace Plan.

    Integration services is up and running fine, it is ruuning under the same account which is my SQL Server is running.

  • February 13, 2012 at 1:57 pm

    #1446501

    Run this before inserting the row:

    EXEC sp_configure 'allow updates', 1

    reconfigure with override

    Then this after:

    EXEC sp_configure 'allow updates', 0

    reconfigure with override

    However, I don't advise inserting rows into system databases. You need to figure out why this row is gone. If you are going to insert/update make sure that the information being inserted/updated is correct.

    Also found this reference, I would scroll down close to the bottom to see the "real" solution, not the moderators response.

    Jared
    CE - Microsoft

  • February 13, 2012 at 2:00 pm

    #1446502

    Here is a better option:http://blog.universql.com/sql-server-2005/the-ssis-subsystem-failed-to-load/

    EDIT: 1 more for good luck! http://qa.sqlservercentral.com/scripts/Backup+%2F+Restore/71216/

    Jared
    CE - Microsoft

  • February 14, 2012 at 9:18 am

    #1446913

    I tried everything in all this you mention but didn't work any of this for me.

    I didn't try below solution because i am still looking, what this fix actual does.

    [font="Arial Black"]EXEC sp_configure 'allow updates', 1

    reconfigure with override

    GO

    --

    update msdb.dbo.syssubsystems

    set subsystem_dll= replace(subsystem_dll,'D:\Program Files','C:\Program Files')

    FROM msdb.dbo.syssubsystems

    where subsystem_dll like 'D:\Program Files%'

    --

    EXEC sp_configure 'allow updates', 0

    reconfigure with override

    GO[/font]

  • February 14, 2012 at 9:32 am

    #1446923

    the first thing is that you cannot update system tables manually without this:EXEC sp_configure 'allow updates', 1

    reconfigure with override[/sql]

    It will not let you. The other code is specific to your machine.

    Instead of doing the insert, you should delete everything from the table and then run: EXEC msdb.dbo.sp_verify_subsystems 1 which will repopulate the table.

    Jared
    CE - Microsoft

  • February 14, 2012 at 10:00 am

    #1446943

    I agreed with that other code is according to my machine.

    We are not installing SQL On default path, we have created here D:\MSSQL for installtion.

    I tried with delete thsoe entry from table and exec this SP to populate the table but it comes with only 10 rows, still i am missing that row in database.

  • February 14, 2012 at 10:47 am

    #1446965

    I think you need to not worry about matching the other databases. Your issue is with the current database and analyzing system tables between the 2 will not help. Did you try now that you have the 10 rows? Did you delete the rows AFTER setting allow updates = on?

    Jared
    CE - Microsoft

  • February 14, 2012 at 12:52 pm

    #1447024

    Yes i did after update on but still showing me only 10 rows.. 🙁

  • February 14, 2012 at 12:56 pm

    #1447027

    Did you try to run the package again? You are not giving me enough details about what you are trying and what is happening. You should not worry about the amount if rows in that table. Why do you persist on thinking that it must have more than 10 rows? That is not a rule nor a guideline. You have to make sure that it is synced with the installed files. If the row is missing, this component may not be installed correctly.

    Jared
    CE - Microsoft

  • February 14, 2012 at 2:02 pm

    #1447049

    Hi,

    After that Exec Sp, i have only 10 Rows in system table. if now i am trying to even create SQL Job which is running SSIS Package it will let me create even. I am getting below error.

    Msg 14234, Level 16, State 1, Procedure sp_verify_subsystem, Line 21

    The specified '@subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems).

    I am missing SSIS Subsystem row in that table.

  • February 14, 2012 at 2:04 pm

    #1447050

    In that case, I would reinstall or repair SSIS.

    Jared
    CE - Microsoft

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply