ssis - Microsoft.ACE.OLEDB.12.0 is not registered

Question

I have a SQL Server job that runs monthly that runs in server. Job is using an SSIS package and is supposed to extract the data from database and and create an Excel sheet and copy the data into Excel 2003.

I actually got around 140,000 rows from the database due to truncation issue in Excel 2003 (Excel supports 64,000 rows). So I modified the config file to support 2007 Excel format.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="  +  @[User::FullPath]  + ";Extended Properties="Excel 12.0;HDR=YES"

But when I try to execute the job, it fails showing error message:

"The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered"

Answer 1

Summarized: INSTALL 32 bit version of Microsoft Access Database Engine 2010 Redistributable. Uninstall 64 bit version if previously installed. http://www.microsoft.com/en-us/download/details.aspx?id=13255

The Excel connection manager is trying to use the ACE OLE DB provider in order to access the Excel file when the version is above 2007 (xlsx). Although your box is 64-bit, you’re using SQL Server Data Tools, which is a 32-bit application. There is no 64-bit version for SSDT. When you design your package within SSDT, you’re using a 32-bit process, which can only use 32-bit providers. When you try to choose the table in the Excel file, the connection manager needs to access the 32-bit version of the ACE OLE DB provider, but this provider is not registered on your machine, only the 64-bit version is installed.

You should download the 32-bit version of the “Microsoft Access Database Engine 2010 Redistributable”. When you try to install it, you might get an error message. You should first uninstall only the 64-bit version of the “Microsoft Access Database Engine 2010 Redistributable”, which you probably installed previously. The 64-bit version and the 32-bit version can’t live together on the same host, so you’ll have to uninstall (through “Program and Features”) and install the other one if you wish to switch between them.

Once you finish uninstalling the 64-bit version and installing the 32-bit version of the provider, the problem is solved, and you can finally choose the table within the Excel file. The Excel connection manager is now able to use the ACE OLE DB provider (32-bit version) in order to access the Excel file.