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:
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.
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:
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.