SSIS package runs in Visual Studio but fails when executed from SQL Agent

Question

Using SQL Server 2014 with Visual Studio 2013 Shell. Relatively new to using SSIS but have had success with creating and executing other packages.

I'm having an issue with one SSIS package in a project running without failing in the first few seconds of the job being kicked off by the SQL Server Agent. I have other packages in the same project that are scheduled and run as schedule with no issues.

The package in question runs with out any issues in Visual Studio, but when it is deployed it will not run. The package details are:

it runs an FTP task to pick up a Excel file from a remote FTP server
then runs a Execute SQL Task to truncate a table
then runs a Data Flow Task that loads the table with the data from the file that was just picked up.

Again this all works fine without errors in Visual Studio. Looking the debug logs in VS there are not even any warnings and takes a little over a minute to run.

I have rebuilt the package in a new package deployed it and it again won't run when deployed, same results. When the deployed package is run it fails right away within 4-15 seconds which is not enough time for it to download the file.

I have other packages doing similar tasks that are deployed and working just fine.

I've seen a couple of things in my searching for a solution but don't think they apply here: There is only one user (me) and I have a proxy setup for SSIS Package Execution which is the same for all the packages. So I don't think it is a security issue.

The Access Database Engine 2010 (redistributable) installed, ACE I believe I seen it referred to. So I don't think it is an issue with drivers.

Anyone able help guide me in where I should be looking or what the issue might be? Any additional information needed? Much appreciated.

EDIT: Additional information from the log file for this job:

-1071611876 (Data Code) SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Data flow engine has finished a call to a component method. : 122 : Excel Source : AcquireConnections : 130832947393433501 : 130832947395273663

-1073450985 (datacode) Excel Source failed validation and returned error code 0xC020801C.

One or more component failed validation.

There were errors during task validation.

from job history you can see at what point it is failing and details of the error will guide you to find the culprit. — Hakunamatata, Aug 06 '15 at 00:26
Looking up that error code I tried setting Delay Validation to True which did not help. Continuing to search. — Neil, Aug 06 '15 at 00:55
See also [Exhibit A](http://stackoverflow.com/a/13960063/181965) and [Exhibit B](http://stackoverflow.com/a/13092260/181965) — billinkc, Aug 06 '15 at 01:33
How is the Excel connection configured? Is it referencing a network drive accessible to the SQL Server Agent? — Alessi, Aug 06 '15 at 06:00
`Execution Option` tab in SQL Agent Job Step needs to have the 32 bit mode checked. — KrazzyNefarious, Aug 06 '15 at 07:18

score 2 · Answer 1 · answered Aug 06 '15 at 13:48

So it was the 32-bit run time box needing to be checked in the SSA Job Step Properties Advanced tab. I think I might have saw that some place but figured it wouldn't apply since SQL Server, Office/Excel and ACE are all 64-bit in my system. But I guess Microsoft hasn't gotten around to making all the drivers 64-bit. Thanks for the help.

SSIS package runs in Visual Studio but fails when executed from SQL Agent

1 Answers1