Let's say you want to load data from an Excel 2003 (.xls) file into a database using SSIS:
If you're running on a 64-bit machine, you'll get this:
Plus this error:
[PASS Regional Mentor Spreadsheet ] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Not good. There is no 64-bit JET driver, and the JET driver is used by the Excel ConnectionManager. Phooey. Sad face. Bummer.
So how do you get around this? One quick and easy way is to execute this package using the 32-bit debugger. To change the default setting, right-click the Project in Solution Explorer and click Properties:
When the Properties dialog displays, click the Debugging page from the list on the left and then change the Run64BitRuntime property to False:
When you execute the package in the debugger (or in the runtime), it will now succeed: