[sql-server] SSIS Excel Connection Manager failed to Connect to the Source

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.)

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.

I found that my excel file that was created in Excel 365 was incompatible with any of the versions available. I re-saved the excel file in 97-2003 version and of course chose that version in the dropdown list and it read the file OK.

As discussed in the below:

Solution: Go to https://marketplace.visualstudio.com/items?itemName=ProBITools.MicrosoftAnalysisServicesModelingProjects and install the latest version, it has a fix in there to resolve this issue.

Reference: https://developercommunity.visualstudio.com/content/problem/745991/could-not-load-file-or-assembly-microsoftdatawareh.html

A fix for this issue has been internally implemented and is being prepared for release. We’ll update you once it becomes available for download. For now, please install latest SSAS from https://marketplace.visualstudio.com/items?itemName=ProBITools.MicrosoftAnalysisServicesModelingProjects to work around the issue. Sorry for any inconvenience.

you can try this:

Uninstall office365

then install only Access Database Engine 2016 Redistributable 64 bit

Also set Project Configuration Properties for Debugging Run64BitRuntime = False

It should work.

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

Here's the solution that works fine for me.

I just Saved the Excel file as an Excel 97-2003 Version.

After researching everywhere finally i have found out temporary solution. Because i have try all the solution installing access drivers but still i am facing same issues.

For excel source, Before this step you need to change the setting. Save excel file as 2010 format.xlsx

Also set Project Configuration Properties for Debugging Run64BitRuntime = False

1. Drag and drop the excel source
2. Double click on the excel source and connect excel. Any way you will get an same error no table or view cannot load....
3. Click ok
4. Right click on excel source, click on show advanced edit.
5. In that click on component properties.
6. You can see openrowset. In that right side you need to enter you excel sheet name example: if in excel sheet1 then you need to enter sheet1$. I.e end with dollar symbol. And click ok.
7. Now you can do other works connecting to destination.

I am using visual studio 2017, sql server 2017, office 2016, and Microsoft access database 2010 engine 32bit. Os windows 10 64 bit.

This is temporary solution. Because many peoples are searching for this type of question. Finally I figured out and this solution is not available in any of the website.

I faced the same issue. I think @Rishit answer helped me. This issue is related to 32 bit/ 64 bit version of driver. I was trying to read .xlsx files to SQL Server tables using SSIS

• My machine was pre-installed with Office 2016 64 bit on Win 10 machine along with MS Access
• I was able to read excel 97-2003 (.xls) files using ssis, but unable to connect .xlsx files
• My requirement was to read .xlsx files
• Installed AccessDatabaseEngine_X64 to read xlsx, that given me the following error:

• I uninstalled the AccessDatabaseEngine_X64 and installed AccessDatabaseEngine 32 bit, that resolved the issue

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.

You need to use an older version of the data connectivity driver (2007 Office System Driver: Data Connectivity Components) and select Excel version 2007-2010 in the connection manager configuration window. I assume the newest data connectivity driver for Office 2016 is corrupt

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.

My answer is very similar to the one from @biscoop, but I am going to elaborate a bit as it may apply to the question or to other people.

I had a .xls that was an extraction from one of our webapps. The Excel connection would not work (error message: "no tables or views could be loaded"). As a side note, when opening the file, there would be a warning stating that the file was from an online source and that the content needed activation.

I tried to save the same file as an .xlsx and it worked. I tried to save the same file with another name as an .xls and it worked too. So as a last test I only opened the source .xls file, clicking save and the connection worked.

Short answer: just try and see if opening the file and saving does the trick.