Can't run dts package job

I setup a job which call a dts package (I try to call it from file system and from SSIS Package Store with the same result) and it doesn't run.

If you take a look from the SQL Server Agent you could see this >>
2012-06-17 09:01:02 - ! [LOG] Step 1 of job 'prueba' (0x85B4D5E843DB3145A6A1A6A0A43D04F3) cannot be run because the DTS subsystem failed to load. The job has been suspended
Any ideas?This was happening to people that installed to a drive other than C:. Did you install to a different drive? It's a bug in setup. If so, you can fix it by changing the dbo.syssubsystems table in MSDB for the dts subsystem.

Look to see where it's pointing. It should be something like this:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL

If the drive is different then the one you installed on, correct it. It should work then.

Please let me know if this worked.
Thanks,|||I saw what you said. I installed SQL in D drive in a directory called SQL_Server. The path you told me was right but the path in the agent_exe field was wrong.

Thanks for your help.|||Excellent! Glad to help out.|||

This was a dts in SQL 2005, right? If so, how did you get to the dbo.syssubsystems table? I know its in the MSDB but I can't get access to that table, even as an admin of the box. Any ideas?

|||

Hi,

I have a similar situation. We have program files on C:\ and Data files on D:\. I ran the following sql to update the syssubsystems table, but I still get the " 2007-01-22 12:13:37 - ! [LOG] Step 1 of job 'MaintenancePlan 1' (0xC433119AFB756E4E844D94759A65B68A) cannot be run because the SSIS subsystem failed to load. The job has been suspended" message.

INSERT INTO [msdb].[dbo].[syssubsystems]

([subsystem_id]

,[subsystem]

,[description_id]

,[subsystem_dll]

,[agent_exe]

,[start_entry_point]

,[event_entry_point]

,[stop_entry_point]

,[max_worker_threads])

VALUES

(11

,'SSIS'

,14538

,'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn\SQLDTSSS90.DLL'

,'C:\Program Files\Microsoft SQL Server\90\DTS\Connections\Binn\DTExec.exe'

,'DtsStart'

,'DtsEvent'

,'DtsStop'

,200);

Do I need to restart the Intergration services service?

Paul

|||

janislkovach wrote:

This was a dts in SQL 2005, right? If so, how did you get to the dbo.syssubsystems table? I know its in the MSDB but I can't get access to that table, even as an admin of the box. Any ideas?

Did you log in as a SQL Server admin?|||

Yes I can login to the box with Domain admin, then onto the Database engine with the SA id and password. I run the sqlagent job as a 'SQL Server Intergration Services package' and it fails with the 'failed to load susbsystem message'. If I run it as an 'Operating system(CmdExec)' i.e dtexec / SQL "[package name]" etc, then it works.

I can obviously get round the problem this way, but it's frustrating it doesn't work as it should. I think it started to happen after SP1 was installed.

Is it something to do with what account Integration Services runs under? As all the other SQL server service run as domain admin, my intergration Services service runs as NT authority\network service. Should I run this as domain admin also?

One further thing, when comparing other boxes with my problem box, is that I have DTEXEC.exe & DTAttach(and associated Dll's) in C:\Program Files\Microsoft SQL Server\90\DTS\Connections\Binn on the problem box whereas they are in C:\Program Files\Microsoft SQL Server\90\DTS\Binn in all other ones. Is this my problem? Can I simply move them to C:\Program Files\Microsoft SQL Server\90\DTS\Binn?