The SSIS subsystem failed

Question

The SSIS subsystem failed

Jinu43

Ten Centuries

Points: 1237
More actions
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.

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

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

Jared SSC Guru Points: 61793 More actions · Answer 1

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

Jared SSC Guru Points: 61793 More actions · Answer 2

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

Jared
CE - Microsoft

Jinu43 Ten Centuries Points: 1237 More actions · Answer 3

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.

Jared SSC Guru Points: 61793 More actions · Answer 4

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

Jared SSC Guru Points: 61793 More actions · Answer 5

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://www.sqlservercentral.com/scripts/Backup+%2F+Restore/71216/

Jared
CE - Microsoft

Jinu43 Ten Centuries Points: 1237 More actions · Answer 6

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]

Jared SSC Guru Points: 61793 More actions · Answer 7

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

Jinu43 Ten Centuries Points: 1237 More actions · Answer 8

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.

Jared SSC Guru Points: 61793 More actions · Answer 9

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

Jinu43 Ten Centuries Points: 1237 More actions · Answer 10

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

Jared SSC Guru Points: 61793 More actions · Answer 11

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

Jinu43 Ten Centuries Points: 1237 More actions · Answer 12

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.

Jared SSC Guru Points: 61793 More actions · Answer 13

In that case, I would reinstall or repair SSIS.

Jared
CE - Microsoft