SQL Server SSIS using Excel fails with DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

If you're running a 64bit SQL Server, you likely are also running the 64bit version of Business Intelligence Development Studio. Unfortunately, the 64bit version of BIDS doesn't work with the Excel Connection Manager. Luckily though, you can still use it to create your SSIS package and getting the package to run is as simple as checking a box.

If you're running SQL Server 2008 and using a SQL Server Agent job to execute your SSIS package that contains your Excel connection, here's how to get it working:

1. Go into the Job Properties
2. Select the step containing your SSIS package and click Edit
3. In the step properties, go to the Execution options tab and check the box labeled "Use 32 bit runtime" (see screenshot)

This will force the SQL agent to use the 32 bit runtime when executing the package, and that supports the Excel Connection Manager.

If you're using SQL 2005 of DTEXEC to execute your package, check out the original post I found that helped me.