0

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?

Bich Le The
  • 27
  • 8
  • Were you able to successfully install the 32bit provider? Sorry - wasn't clear if that was a success or not. When you are using the editor, this is 32 bit mode. `Run64BitRuntime` applies only when running the package in debug. Often you need both 32 and 64 installed to configure and run the package the way it will run once deployed. – Mark Wojciechowicz Apr 12 '23 at 18:35
  • Yes, the provider was successfuly installed. So the Run64BitRuntime is not relevant in my situation where I try to select sheet from excel in the connection manager. – Bich Le The Apr 13 '23 at 10:48

0 Answers0