CODEWITHSUNDEEP
sql-serverexcelssisoledbetl
Dhivakhar Venkatachalam
Dhivakhar Venkatachalam

Reputation: 385

Unable to Connect to Excel via SSIS

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.

Upvotes: 4

Views: 9184

Answers (5)

John
John

Reputation: 11

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

Upvotes: 1

Joan Wanjiru N
Joan Wanjiru N

Reputation: 1

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

Upvotes: 0

ocean800
ocean800

Reputation: 3737

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

Upvotes: 0

Hadi
Hadi

Reputation: 37368

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

Upvotes: 1

J Sidhu
J Sidhu

Reputation: 677

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

Upvotes: 0

Related Questions