Executing SSIS Package as SQLAgent Job

Question

I have a SQL Server 2008 SSIS job which executes some Win 32 exe files. This job runs fine when i execute the job through BIDS (On Windows 2008 box). Now i have deployed the package in MSDB database and trying to execute the package as SQLAgent Job.

When i do this the job fails and throws an error message while executing the Win 32 exe. I'm able to run the exe from a batch file on the same server. But when SQLServer agent job tries to execute it throws below error

Code: 0xC0029151, The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1).

Any ideas?

Solution 1

My assumption is the server is a 64 bit server, yes? If so, the agent will be running SSIS In 64 bit mode and that's causing an incompatibility with the executables.

In the agent job, on the Execution Options tab, check the "Use 32 bit runtime" and it should be fine.

The question is tagged as 2008 but if I recall correctly, 2005 SQL Agent tasks of type SQL Server Integration Services Package did not have an option for running in 32 bit mode and by default everything ran in 64 bit mode. To get around that, one needed to run an OS command and call out to the 32 bit dtexec.exe which was most likely found in C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe

Solution 2

This also needs to be changed on BIDS. Solution Explorer> Right Click Project Properties>Configurations Properties>Debug Options>Run64BitRuntime - False

Comments

[Phoenix]

Hello @billinkc: Thanks for the reply. I in fact tried with 32 bit run time option but it didn't work. When i run the package from MSDB database directly, it is getting executed fine. And I've changed the compatibility settings of the problematic exe to windows 2000.

[billinkc]

On the development machine where it works fine from BIDS, try running the package from the command line. C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /file C:\src\package.dtsx and if your development box isn't 64 bit, then drop the ` (x86)` part. Does the invocation work or fail on your box?