No Tables or Views when Connecting to Excel file in SSIS

Question

Visual Studio 2019 Excel 365 64-bit Excel file has a single worksheet named raw_products

• I added a Data Flow Task to my Control Flow
• Added Excel Source to the Data Flow
• Opened the Excel Source and clicked "New" to create a new connection manager.
• Selected my Excel file (Noted that the Excel version changed from 97-2003 to 2007-2010 after I selected the file).
• I selected Table or View for Data Access Mode and was greeted with the error:

  No Tables or Views could be loaded
  
  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'
  

I went through and tried every version available and none would connect. A quick search on SO shows that the fixes are anywhere from reverting to Windows 98 or reinstalling VS to installing 32bit or 64bit Access Database drivers.

The purpose of this task is to import the Excel file into my SQL database. If I use the SQL Import Wizard it works perfectly, and I actually saved it as a SSIS package and imported it into the package I'm working on now.

I've tried all the versions available in the connection manager, and I installed the 64bit Access Database Engine drivers (it won't allow me to install the 32bit since I have 64bit office installed). I tried changing the Run64BitRuntime option in the project properties. I don't want to reinstall VS2019, it took me 4 hours to get it and the Integration Services installed!

EDIT: Against my better judgement I removed VS2019 and Integration Services. It took me almost 8hrs to get them reinstalled and for everything to work again since MS Visual Studio Tools were not removed or registered correctly.

I have reinstalled everything, rebooted at least a dozen times, and I am still having the same issue where it will not show anything. VERY frustrating since this is the most important part of the SSIS package.

Answer 1

You need to Connection manager:

• One for the source (Excel) that you already added
• One for the target/destination (SQL)

Do you have the second one with SQL? Can you test it if it is successful?

Once it is successful you will have list of tables and you can choose your target table from the list if it exists.