DTS_E_OLEDB_EXCEL_NOT_SUPPORTED

The current majority opinion is that the current Office OLEDB provider (Microsoft.ACE.OLEDB.12.0) and 64-Bit environment can’t work together. In fact there are some problems using the provider in a 64Bit environment. If you want to know how to use it in SSIS the right way read this article.

Some facts about the Office OLEDB provider and SSIS:

Currently the Office OLEDB provider is 32 bit only!

That is wrong! Microsoft provides a 64 bit version since December 2010. You can download it here: Microsoft Access Database Engine 2010 Redistributable

Developing SSIS packages with the 64 bit Version of the provider does not work!

Unfortunately Microsoft provides Visual Studio as 32 bit application only (Why?). Therefore it can’t access/use 64 bit OLEDB providers. As a consequence of that fact you cannot select the Office OLEDB provider. In an development environment you have to use the 32 bit provider.

Excuting SSIS packages in an 64 bit environment with 64 bit OLE DB driver does not work!

Yes and no! Using the original Excel-Source does not work. It seems that Microsoft implemented an hard if-statement which throws an exception (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.) if these conditions are met:

·         DTS-Engine runs in 64 bit environment

·         Connection string is “Microsoft.ACE.OLEDB.12.0″

One solution for that is to use the standard OLEDB Source. Select Microsoft.ACE.OLEDB.12.0 as OLEDB provider and set theextended properties to “Excel 14.0;HDR=YES”. Now you can use the normal OLEDB source to retrieve data from an Excel file. I found no restrictions so far.

Excel has to be installed where the SSIS package is to be executed!

That is wrong. You only need the Microsoft Access Database Engine 2010 Redistributable

The lesson is clear: Using Office OLEDB in a 64 bit environment with SSIS works (At least at the command line with DTEXEC)!