Executing SSIS Package as SQLAgent Job
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?
Answer
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