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
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 ?
Right click on the SSIS project and select Properties. In
Configuration Properties, select Debugging and change the
Run64BitRuntime to True.
This will allow a package running in Debug mode to use the 64 bit architecture instead of 32 bit.
Remember to deploy the ACE drivers on the server once you attempt to move the package from your machine to an actual server.
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:
User contributions licensed under CC BY-SA 3.0