Visual Studio 2019 - SSIS - Excel Connection Manager fails to connect

VJF326 21 Reputation points
2022-09-02T14:35:47.577+00:00

I am running SQL Server 2019 on a Windows 11 machine. I have installed Visual Studio 2019 along with SSIS version 16.0.694.0. In addition, I've installed Access Database Engine 2016 redistribute. I am running Microsoft 365 version 2207 build 15247.20210 C2R.

When connecting to an Excel workbook, I get the following error:

TITLE: Microsoft Visual Studio

------------------------------

Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
Failed to connect to the source using the connection manager 'Excel Connection Manager'

------------------------------

BUTTONS:

OK

------------------------------

Error at Package2 [Connection manager "Excel Connection Manager"]: 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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,454 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 24,946 Reputation points
    2022-09-02T15:55:37.07+00:00

    Hi @VJF326 ,

    You need to make sure that you have the following settings on your developer workstation:

    • The Run64BitRuntime VS2019 project property is set to False.
    • The Microsoft Access Database Engine 2016 redistribute, 32-bit edition is installed.

    All these settings are just for a development workstation.
    SSIS run-time server could/should use Microsoft Access Database Engine 2016 redistribute, 64-bit edition.

    Installation notes
    If your machine already has MS Office 64-bit installed, you will need to go to a command prompt and add one extra parameter to install Microsoft Access Database Engine 2016 redistribute, 32-bit edition: 

    c:\>AccessDatabaseEngine.exe /quiet

    Here is all available switches for the installer:

    237336-microsoft-access-database-engine-2016.png

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 48,396 Reputation points
    2022-09-02T16:13:30.697+00:00

    The error message points to the most common problem - your package is running as x64 but you installed the x86 ACE driver. That won't work.

    The quick check is to adjust your SSIS package to run using the x86 host. If it works then that is the issue.

    Ideally you should install the x64 version of the driver and let SSIS continue to be x64. However you cannot install the x64 driver on a machine that has x86 Office installed. In that case you'll have to either remove Office x86 or stick with x86 driver for now.

    0 comments
  2. Cyr, Paul 0 Reputation points
    2024-02-22T18:39:05.7166667+00:00

    In Visual Studio 2022 my Run64BitRuntime is greyed out: I also tried installing the 32 bit version with the AccessDatabaseEngine.exe /quiet and rebooted and Visual Studio 2022 is still throwing the error message. Any suggestions?