SSIS and Excel on a 64 bit Server


By Mark Wojciechowicz, Anexinet Business Intelligence Architect
Recently, I developed a package which read the contents of an excel file and inserted rows into several SQL tables.  Note that development was on a 32 bit machine.  When I deployed the package to our 64 bit dev server, I received the following 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.

Todd McDermid offers good advice regarding this problem on the MS forums: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/289e29ad-26dc-4f90-bad4-ffb86c76e5f9/

In my case, I have a master package executing many other packages.  I did not want to have the whole master run in 32-bit mode, so I replaced my execute package task with an execute process task and then referenced the 32 bit DTEXEC.exe.  I then ran the package again, but received a new error:

[Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0×00000000.

An OLE DB record is available.  Source: “Microsoft OLE DB Service Components”  Hresult: 0×80040154  Description: “Class not registered”.

Now, the issue was that MS Office was not installed on the machine.  Luckily, you can install the data providers from MS:

Microsoft Access Database Engine 2010 Redistributable
http://www.microsoft.com/download/en/details.aspx?id=13255
However, I installed the 64 bit provider and received the same error.  I then uninstalled the 64 bit, installed the 32 bit and Yahoo – it finally worked.

Final Answer – To read excel on a 64 bit machine in SSIS, you need to install the 32 bit data providers and execute the package from the 32 bit version of the dtexec.exe.