While dealing with SSIS 2012, you might want configure connections to third party platforms, like SQL Server, Oracle DB or even Access or Excel inside your packages data flows. SSIS uses, what Microsoft called Connection Managers to configure this connections, and its easy to think about several scenarios in which your source or destination target could need to use the Excel Connection one, to select or insert data.
The problem this post wants to clarify is only applied to 64-bit environments, to which the Excel Connection manager isn´t compatible. This can be a problem in integration projects, and I speak for my self when I say that several of them, have an initial loading stage in which we get data from several Excel files and integrate them in a SQL Server database for instance.
Imagine that you have installed your SQL Server 2012 with Business Intelligence features in a 64 bit environment however you want to use an Excel Source inside your data flow. The following figure shows you how a simple data flow could manage this requirement.
When you compile and run a package like this, SSIS will try to execute it, by default, in a 64-bit version and you will get the following error:
[Read from source ] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager “YOUR CONNECTION NAME” failed with error code 0xC0209303.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
This error has I have previously explained, occurs because SSIS engine will try to run the package in a 64-bit version and the Excel Connection Manager is only compatible with 32-bits packages. The solution for this problem is very easy, go to the properties of your package (Right click in your project, inside the Solution Explorer – See the following figure).
Although this solution won’t mess with any other Connection Manager inside your project, you must be aware that all your packages, inside this project will now run in 32-bits.