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

Question

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.

2. 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 :).

Answer 1

Hi @Anonymous

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.