How to fix - Excel Connection Manager fails in SSIS

Martin, Daniel 10 Reputation points
2023-05-09T02:44:51.95+00:00

Hi there! I am trying to ingest data from an Excel file (.xlsx) using SQL Server Integration Services (SSIS) through Visual Studio. The versions/specs that I am running are:

  1. Microsoft Visual Studio 2022 - 64 bit version 4.8.04084
    1. SQL Server Data Tools - 17.2.40120.0
    2. SQL Server Integration Services - 16.0.5209.0
  2. Microsoft 365 Apps for enterprise - 16.0.15601.20626
    1. Excel file (xlsx) shows that I have the 32-bit version.

The error I am getting is:

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

I've tried multiple things from this forum and Stackoverflow but cannot seem to resolve the issue. Any help would be greatly appreciated!

Thank you in advance

Dan

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

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,051 Reputation points
    2023-05-09T05:39:46.5433333+00:00

    Hi @Martin, Daniel,

    This error comes when you try to retrieve Excel Sheets from the connected Excel Workbook. In order to solve this, you need to install correct OLE DB Providers and Drivers for Microsoft Office files.

    See: Microsoft Access Database Engine 2010 Redistributable

    Note that if you have 32-bit version of Office, then you need 32-bit drivers else you need 64-bit drivers.

    Also check installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing in case that you will meet the 32bit and 64bit issue.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    1 person found this answer helpful.
  2. Gopi Tanguturi 0 Reputation points
    2023-10-03T23:30:28.55+00:00

    Hi, I am getting the same error on my VS 2022 when trying to access Excel from SSIS, I installed access DB engine 2007 and also Access DB engine 2010 64 bit, no luck, changed the debugging - Run64BitRuntime to false in the project properties, still no luck, not sure what else I need to do to resolve the error. Someone please help me.

    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

    0 comments
  3. Joshua Manning 0 Reputation points
    2023-10-25T20:09:19.73+00:00

    I'm having the exact same issue. I'm able to work with Excel sources with no problems using VS SQL Server Data Tools (SSDT) 2017. It's just Visual Studio 2022 that has the problem. I am using Office 32-bit, so Run64BitRuntime is set to false in the project settings. The connection string calls the Microsoft.ACE.OLEDB.12.0 provider. I can't see what else would be different between the two apps.

    0 comments
  4. Cyr, Paul 0 Reputation points
    2024-02-22T19:12:01.7633333+00:00

    I am experiencing the same issue with Visual Studio 2022 with excel connections

    0 comments