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)!
No comments:
Post a Comment