SSIS Excel Connection Manager failed to Connect to the Source

Question

I have a server that is capable of creating and running an Excel Import task using the Import Wizard. I am trying to automate that process by using a visual Studio 2010 Integration Services package, that I am developing on that server.

The problem happens when trying to design the package. I have added an excel connection and pointed it at the Excel file on a local disk (the same file I have already successfully imported using the import wizard). When I add an Excel Source to the DataFlow and specify the excel connection, when I go to the Name Of the Excel Sheet Drop down I just see "No tables or views can be loaded" and get the following error.

"Could not retrieve the table information for the connection manager. Failed to connect to the source using the connection manager ..."

I can't find this error logged anywhere and i don't know why it is failing. The directory is shared to Authenticated users and the file is not in use.

Any ideas how to debug this error? I understand there can be issues running this in 64 bit mode, but does that apply to development?

I should add that it is an excel 2007 file .XLSX and the connection is set to Excel 2007.

2019-11-08 The answer by GavB841 below looks promising, if anyone tries it and it works please let me know. (I am no longer working in this area.)

Solution 1

It seems like the 32-bit version of Excel was not installed. Remember that SSDT is a 32-bit IDE. Therefore, when data is access from SSDT the 32-bit data providers are used. When running the package outside of SSDT it runs in 64-bit mode (not always, but mostly) and uses the 64-bit data providers.

Always keep in mind that if you want to run your package in 64-bit (which you should aim for) you will need both the 32-bit data providers (for development in SSDT) as well as the 64-bit data providers (for executing the package in production).

I downloaded the 32-bit access drivers from:

• Microsoft Access Database Engine 2010 Redistributable.

After installation, I could see the worksheets

---

Source:

• Extracting Data From Excel with SSIS

Solution 2

The workaround is, I save the excel file as excel 97-2003 then it works fine

Solution 3

I also ran into this problem today, but found a different solution from using Excel 97-2003. According to Maderia, the problem is SSDT (SQL Server Data Tools) is a 32bit application and can only use 32bit providers; but you likely have the 64bit ACE OLE DB provider installed. You could play around with trying to install the 32bit provider, but you can't have both the 64 & 32 version installed at the same time. The solution Maderia suggested (and I found worked for me) was to set the DelayValidation = TRUE on the tasks where I'm importing/exporting the Excel 2007 file.

Solution 4

The recommendations from this article Extracting Data From Excel with SSIS resolved the issue for me.

I downloaded MS Access Database Engine 2010 32 bit driver from the link in that article.

Also set Project Configuration Properties for Debugging Run64BitRuntime = False

In SQL Server 2014 SSMS (Integration Service Catalog -> SSISDB -> Environments -> Projects for all Packages in Validate checked box 32 bit Runtime.

My SSIS packages are working now in both VS 2013 and SQL Server 2014 environments.

Solution 5

Simple workaround is to open the file and simply press save button in Excel (no need to change the format). once saved in excel it will start to work and you should be able to see its sheets in the DFT.

View more solutions

Comments

[PatFromCanada]

I'll try that out when I get a chance, sounds promising.

[Jim]

Try right-click Project name ->Configuration Properties -> Debugging -> Run64BitRuntime -> False

[Hellion]

This worked perfectly for me when I could not down-level my excel file due to having too many rows (over 500,000) for Excel 97-2003 format.

[IdahoB]

Ditto ... the lack of the 32-bit Access driver was key for me. I'm using SQL Server 2017 and Visual Studio 2017. After installing the aforementioned driver, the tabs in the workbook showed up in the Excel data source.

[usersam]

This Solved my problem also. Thanks

[Santhana]

It is working for me with manual intervention, But in schedule how can i achieve it.

[fapw]

Additionally, I had to change Provider in Connection String in Properties window from Microsoft.Jet.OLEDB.4.0 to Microsoft.ACE.OLEDB.12.0

[Ranjith]

Before I use Data Tool 64bit and Ms Office 64bit it returns the error,Then i Install Ms Office 32bit and Change the Properties in Project > Project Properties > Debugging > Run64bitRuntime > (Changed From True to False).Now its working Fine.mssqltips.com/sqlservertip/5324/…

[Pranesh Janarthanan]

I am using VS 2017 preview with SSDT , my pc has a 64 bit os, so i installed AccessDatabaseEngine.exe 32 bit version, and then i tried loading the same, it showed up names of excel sheet. thank you +1

[Scotch]

Did you have to make any changes to the SQL Server that you ended up running this package from?

[Soheila Tarighi]

hi PatFromCanada, how can I save the excel file as excel 97-2003 with ssis packages. because my package will execute with a job so I don't access for save the file manual.

[OnlySalman]

Yeah It's a workaround until you got a file which >65K