Excel Connection Manager in SSIS Data Flow Task fails to connect to spreadsheet

James Ball 0 Reputation points
2023-01-20T19:30:01.7733333+00:00

Hi I am using Excel 2016 64-bit part of MS Office Pro Plus 2016 (from file/account/About Excel)
Microsoft Access Database Engine 2016 Redistributable 64 Bit. From https://www.microsoft.com/en-us/download/confirmation.aspx?id=54920
Visual Studio Community Ver 2019 (2) Version 16.11.23 with SSIS 16.0.5035.3

1 When creating a new connection manager, type Excel. In the Excel Connection Manager dialogue, I add the file path (I can open this spreadsheet in Excel, and the data area is formatted an Excel Table (I also tried it unformatted), the spreadsheet is currently closed. The Excel version is set to 2016. Then hit OK to commit the change.

  1. In a data flow task I add Excel Source to the control surface and open the editor. I choose the Excel Connection Manager, just created (no others are in the package or project). Choose the Table or View Data access mode (default). When I click on the Name of the Excel Sheet down arrow... the dialog box displays No tables or views could be loaded and I see the message (which I've googled) that says:

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'

Thanks for any help :).

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,854 questions
Visual Studio Extensions
Visual Studio Extensions
Visual Studio: A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.Extensions: A program or program module that adds functionality to or extends the effectiveness of a program.
15 questions
Access
Access
A family of Microsoft relational database management systems designed for ease of use.
22 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
81 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 18,896 Reputation points Microsoft Employee
    2023-01-23T02:24:00.3566667+00:00

    Hi @James Ball

    SSDT is a 32-bit IDE. Therefore, when data is access from SSDT the 32-bit data providers are used.

    Please try to install Excel 32-bit for a try.

    You may Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing.

    Regards，

    Zoe Hui

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