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

User · Answer

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  amp  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

User · Answer

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   Drag and drop the excel source Double click on the excel source and connect excel  Any way you will get an same error no table or view cannot load     Click ok  Right click on excel source  click on show advanced edit  In that click on component properties  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   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   quot no tables or views could be loaded quot    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

User · Answer

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

User · Answer

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

User · Answer

Here s the solution that works fine for me   I just Saved the Excel file as an Excel 97-2003 Version

User · Answer

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

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

Examples related to sql-server

Examples related to visual-studio-2010

Examples related to visual-studio

Examples related to ssis

Examples related to sql-server-2012