SSIS - Cannot connect to excel source and load any views or tables. Visual studio version 2022

Question

I have a problem when importing data in SSIS from an excel source.

I have the Visual Studio Professional 2022 installed and excel’s version is Microsoft® Excel® for Microsoft 365 MSO (Version 2301 Build 16.0.16026.20214) 32-bit. My system type is 64-bit operating system, processor for x64 platform.

When I create an excel connection manager in the SSIS, I can’t get pass through the error when I try to select a Name of the excel sheet:

    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 have looked and tried every solution I could have find on this problem. None of them worked for me.

What I have tried:

• downloaded the 32-bit access drivers from: Microsoft Access Database Engine 2010 Redistributable (also the 2016)
• tried to install in command prompt with function /quiet also
• change Provider in Connection String in Properties window from Microsoft.Jet.OLEDB.4.0 to Microsoft.ACE.OLEDB.12.0
• save the excel file as excel 97-2003 (I have also tried older verison of excel .xls and also .xlsx)
• set the DelayValidation to = TRUE
• set Project Configuration Properties for Debugging Run64BitRuntime = False
• tried to instal the SSAS

I am only creating a simple flow where I get data from an excel sheet and put data to server.

I did not find any more solutions on this topic and my problem still persists.

Maybe the newer VS version requires different solution?

Could you give me some advice?

Answer 1

I had the same issue, and all answers seemed to point towards the Microsoft Access Database Engine version but that kept providing an error when trying to install either the 64 bit or 32 bit versions.

What worked for me was that I uninstalled office products and office 365, I also uninstalled both Microsoft Access Database Engines (of which I had both 32x and 64x versions).

I then installed the 64 bit version of Access Database Engine: Microsoft Access Database Engine 2016 x64

Once I done this I restarted visual studio and the excel source editor was able to find the excel sheet.

Answer 2

i changed the default Excel version 97-2003 to 2016 when configuring the destination and it worked fine