I recently created an SSIS package in my 32 bit BIDS on my workstation that connects to an Excel 2007 xlsx file that transfers some data to a 64 bit SQL Server 2008 instance. When I ran this package on my local computer everything worked great but when I tried to execute the package from the SQL Agent at the server it failed with this 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
After some research online I found a great article on msdn for designing and running SSIS packages in a 64 bit environment. I discovered I needed to set the “Use 32 bit run-time” option in the Execution Options tab of the job step. After instructing SQL Agent to run this package in 32 bit mode for the so the Excel provider will work I received a new error (it can’t work on the first try, right?):
The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered
It turns out the Excel 2007 provider was not installed as part of the Integration Services installation, but you can download it from Microsoft here and as far as I can tell it’s 32 bit only. The installation does not require a reboot.
In summary, once I forced the Agent job to run in 32 bit and installed the Excel 2007 provider on the server everything works like a gem.