I have a SQL 2005 SSIS package that fails on an Execute Process task. The package runs successfully in BIDS, but fails when running it on the server. I have a SQL job built to run the SSIS package which uses the SQL Agent account.
The .exe file which the Execute Process is supposed to run is located on the SQL server. I can successfully run the .exe directly, or as I mentioned, in the package within BIDS. I have BIDS running on a separate machine. The package was imported as a File System, and I've tried both ProtectionLevel of sensitive by user and by password. I've changed the SQL agent service to run with an account that has permissions to both the .exe file and SQL, and I've lowered the folder and file permissions where the .exe file is located to everyone.
I have my package config file with just the connection strings added - one connection for the .exe file, and another for my exchange server for a send mail task.
Here is the error from the SQL job logs:
Executed as user: MY_SERVER\myusername. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:29:42 AM Error: 2009-04-20 10:29:43.78 Code: 0xC0029151 Source: Execute RRDFulfillment Mapping Execute Process Task Description: In Executing "E:\MD\SSIS\RRDFulfillment\Debug\Mapping.exe" "" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:29:42 AM Finished: 10:29:43 AM Elapsed: 0.828 seconds. The package execution failed. The step failed.
Any suggestions are welcome.