The requested OLE DB provider Microsoft.ACE.OLEDB.16.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode

Question

I am creating an SSIS package using Visual Studio 2017. I have a Win 10 64 bit machine, and MS Office 2016 64 bit.

Source: SQL Server database

Destination: Excel

On the Excel Connection Manager, I selected Excel Version "Microsoft Excel 2016"

The Connection String that's created is below:

Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\\MyServer\MyFilePath\MyXLSFile20210303.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";

On the Excel Destination, I am getting this error:

The requested OLE DB provider Microsoft.ACE.OLEDB.16.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

My package > Properties > Debugging > Run64BitRuntime is already set to True.

I had installed "Microsoft Access Database Engine 2016 64 Bit" (I downloaded it from https://www.microsoft.com/en-us/download/details.aspx?id=54920).

I couldn't install the 32 Bit version, since I get the error "You cannot install the 32-bit version of Microsoft Access Database Engine 2016 because you currently have 64-bit Office products installed".

How can I fix this error ?

Thank you

Answer 1

It is a little bit more complicated.

Visual Studio 2017 is a 32-bit application. That's why it needs a 32-bit ACE provider installed on the dev. machine.

It is possible to have both editions, i.e.32-bit as well as 64-bit, of the ACE provider installed on the same machine. The /quiet flag at the command prompt allows to do that. Please see a screen shot below.

As end result, you would need the following:

• SSIS dev. machine: 32-bit ACE provider, VS with Run64BitRuntime set to False.
• SSIS run-time on a server, 64-bit ACE provider.