When attempting to execute a package that makes use of an Excel Connection Manager you may be presented with the following errors.
- SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "YourConnectionMgrName" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
- [Connection manager "YourConnectionMgrName" Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
The problem is that Microsoft doesn't provide a 64-bit Excel Connect Manager component (as stated in the error). This means that to run the package it must be run using the 32-bit DTEXEC.exe. When running in BIDS (Visual Studio) it's as simple as setting a project property. Below is the default property on a 64-bit machine. Change True to False to run the package.
If you're running the package under a SQL Server Agent job you must also set a similar property on the job step that runs the package. Notice the Use 32 bit runtime property. Make sure this is checked.