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