4

Excel file is not being recognized by SSIS.

Error :

Could not retrieve table information from connection manager "Excel Connection Manager
Failed to connect to the source using the connection manager 'Excel Connection Manager'

I have tried installing 32-bit of redistributable which resolved this error but when I run this outside using SQL stored procedure it is failing, which is in turn resolved if i install 64 of redistributable but this is failing in SSIS,

Hence, I need 32-bit to run in SP, and 64-bit to run in SSIS but I cannot install both at the same time. Is there any solution to this issue.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Dhivakhar Venkatachalam
  • 345
  • 2
  • 16
  • You are "running outside using SQL stored procedure" and "Running in SSIS". I assume these mean running the package with `catalog.create_execution` and running in SQL Server Data Tools. Please edit your question to be specific. There is enough confusion on this topic without vague descriptions. Regardless, there are many guides on how you can get 32+64 bit ACE installed together. I don't have a link to it but this guy managed to do it. https://stackoverflow.com/questions/61581965/is-it-actually-possible-to-create-ssis-packages-in-a-64-bit-environment-that-use#comment109424918_61581965 – Nick.Mc May 28 '20 at 23:37

5 Answers5

1

There are 2 solutions:

(1) Run package in 32 bit in SSIS

Try running the package in 32-bit mode:

GoTo Project properties >> Debugging >> Run64BitRuntime  = False

enter image description here

(2) Install both AccessDatabaseEngine 32bit and 64bit

Assuming that you meant the Access Database Engine by "redistributable"

If you need to install AccessDataBaseEngine x64 alongside with 32-bit installation, you need to run the installation in passive mode:

Passive mode installation steps

  1. Open the Command Prompt by typing cmd in the Windows search box under the Start menu and selecting cmd.exe
  2. Type the file path and file name of the 64-bit Access Database Engine 2010 install file, followed by a space and /passive (this runs the installation without showing any error messages).
  3. Open the Registry Editor by typing regedit in the Windows search box under the Start menu and selecting regedit.exe

  4. Delete or rename the mso.dll registry value in the following registry key: 

    "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths"

More details and screenshots are found in the link below

Reference

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hi, I have tried this but unfortunately installing both versions is not helping.Once 32bit is installed if i passively install 64bit the SSIS package is not working. As a workaround I am uninstalling and installing the relevant packages(32/64) as I work. – Dhivakhar Venkatachalam Mar 21 '19 at 06:12
  • @DhivakharVenkatachalam can you provide the procedure code – Hadi Mar 21 '19 at 07:38
  • 1
    You should persevere with getting both versions installed side by side. This is the usual solution – Nick.Mc May 28 '20 at 23:38
  • Hello. Is it right that excel and access dont work when package is run in 64 bit mode? And that in production you must use 32 bit mode. There are conflicting articles on the topic. – variable Mar 30 '21 at 18:44
  • @variable It is a while that I didn't work with Excel or Access. But as I remember, It depends on the AccessDatabaseEngine and the Office version installed. Make sure you installed both 32bit and 64bit database engines as mentioned in the answer above. – Hadi Mar 31 '21 at 11:19
1

If you like to have the new format of Excel (xlsx) - version 2007 and so on, you can install Access. That combined with running the package in 32 bit mode will hopefully solve the problem.

https://www.microsoft.com/en-us/download/details.aspx?id=54920

John
  • 11
  • 1
0

You can keep both as 32 bit by running the ssis in 32 bit mode by using dtexec 32 bit utility.

DTExec 32 bit is at: 

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn

DTExec 64 bit is at: 

C:\Program Files\Microsoft SQL Server\90\DTS\Binn

refer to this to see how to run

Hadi
  • 36,233
  • 13
  • 65
  • 124
J Sidhu
  • 677
  • 1
  • 4
  • 19
0

Since I came across this problem with the same error code--

You could also have the wrong of version of excel selected when you create the Connection Manager, i.e Excel 2007-2010 vs Excel 2016, etc

ocean800
  • 3,489
  • 13
  • 41
  • 73
0

1.click on project>>properties>>debugging: change the Run64BitRuntime to false.

2.If that does not work, save you excel work using Excel 97-2003 workbook format, and so should your excel connection manager in SSIS.

3.Also trying reading the data from the workbook while its open

Joan
  • 1