SSIS 64 bit vs 32 bit

Question

I am developing an SSIS package in VS 2012 that is being deployed to SQL Server 2012. The package is pulling data from an external database with a 32 bit driver and loading it into SQL Server 2012. I have the package set up so that it is using project parameters to store the connection strings.

I can run the package from VS and I can also run the package via 32 bit dtexec. I cannot run the package from the SQL agent. I have the job set up so that it is using a ssis proxy account.

I have also tried to run a package with an execute process task that runs the package in 32 bit dtexec.

I get the following error in all cases. It seems to be either a 32/64 bit issue or a permissions issue although I cannot figure out what it is. Any help would be greatly appreciated!

Data Flow Task:Error: The AcquireConnection method call to the connection manager MyDataBaseConMgr failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Answer 1

Unlike SSDT, SQL Server agent runs on 64 bit mode. You can configure SQL job to run on 32 bit mode by traversing to

Steps > Edit > Configuration > Advance