locked
SSIS project error: could not retrieve the table information for the connection manager 'excel connection manager' RRS feed

  • Question

  • I'm new to SSIS. For my practice, I want to transfer data from excel to SQL Server.

    1. I have created a connection manager for MS SQL Server (by selecting Connection manager for OLE DB Connections)

    2. Another connection manager for EXCEL file

    3. I have added an excel source to the Data Flow.

    4. Now, I'm trying to edit the Excel source so that I can view the data inside the Excel file which throws following error.

    "could not retrieve the table information for the connection manager 'excel connection manager' Failed to connect to the source using the connection manager ...

    After searching a lot on Google including stackoverflow, I found several causes for this error. 

    1. SQL Data Tools is for version 32bit. So, install "Microsoft Access Database Engine 2010"

    2. Change DataMigration Property page configurations.  Change Run64BitRuntime to False. 

    3. Change Excel Connection manager options "Excel Version" to "Microsoft Excel 97-2003" and to other options as well.

    4. Make sure that excel file that I am trying to pull data from is not open while doing all these.


    Tried every option found on the Internet but nothing worked. Now, I just want to get this fixed no matter what. Any suggestions to fix this issue would be greatly appreciated.

    [I wanted to include screenshots of everything but it says I must be a verified user to do so. So, pls help me out]

    Thanks and regards,

    Sushma


    • Edited by sushmaGS Tuesday, April 4, 2017 10:53 PM
    Tuesday, April 4, 2017 10:51 PM

All replies

  • Hi Sushma,

    Do not walk in the dark, check what version of Excel you have installed, bitness vs. what SSIS is using driver.

    What SSIS version is also important because the older ones support the older versions of Excel.

    Likely, you are trying to process a newer version of the Excel file than your SSIS driver.

    So yes, update the driver (opt 1).


    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Pirlo Zhang Wednesday, April 5, 2017 8:04 AM
    Tuesday, April 4, 2017 11:23 PM
  • Hi sushmaGS,

    In addition to ArthurZ, from the error message, this error comes when you try to retrieve Excel Sheets from the connected Excel Workbook. Please ensure you install correct OLE DB Providers and Drivers for Microsoft Office files. If you have 32-bit version of Office, then please download 32-bit drivers, else download 64-bit drivers.

    If it still does not work, please let me know.

    Regards,
    Pirlo Zhang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 5, 2017 8:04 AM
  • Hi sushmaGS,

    If your issue is solved please mark the appropriate answer as Mark as answer. This will help other members to find a solution if they face the same issue.

    Thanks for your understanding and support.

    If you still have any questions, please feel free to ask.

    Best Regards,
    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 10, 2017 6:51 AM
  • It worked for me, I just installed this file, {32 bit} (https://www.microsoft.com/en-za/download/confirmation.aspx?id=13255)
    • Proposed as answer by Pirlo Zhang Thursday, September 21, 2017 9:02 AM
    Thursday, September 21, 2017 8:53 AM
  • I had the same problem. The solution is simple. Just resave your excel sheet in (97-2003) format and everything will be fine. 

    Friday, December 22, 2017 3:14 PM
  • As a side note always check your excel with your  provider in the connection string.

    i.e  "Provider=Microsoft.ACE.OLEDB.12.0"


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s). http://sqldataside.blogspot.ca/ (SQL Tabular + PowerShell)

    Wednesday, December 27, 2017 2:37 AM
  • Nik,

    Thanks for your final note here!  I have been banging my head all day on this error and just changed my connection string from 16 to 12 and it works!  Can I buy you a beer?

    Brad


    Brad Allison

    Tuesday, July 31, 2018 6:18 PM
  • Hi Sushma,

    I was also facing the same issue and had applied all those solution which you did. But, it was not worked for me. Finally, I Restarted my machine and executed package. Package executed successfully.

    Wednesday, September 4, 2019 9:11 AM
  • I have the same problem with 2 kind of files:

    1. I do can read other Excel files except the one that has been generared by an application. The extension is *.xlsx and I open it without problem with MS Office Excel. 

    Connection String: 

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Downloads\Report11.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

    2. Error  to read files with macros: *.xlsm. The conection string to this kind of files is rigth because it identifies that the file has macros.

    Connection String: 

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Weekly KPIs Database.xlsm;Extended Properties="Excel 12.0 MACRO;HDR=YES";

    The error message in both cases is the same:

    TITLE: Microsoft Visual Studio
    ------------------------------
    Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
    Object reference not set to an instance of an object.
    ------------------------------
    BUTTONS:
    OK
    ------------------------------
    Wednesday, November 13, 2019 9:42 AM
  • Hi Melianacc,

    Please share a screen shot of the VS where you are getting the errors.

    I recently answered a similar question.

    Check it out: Excel Source stopped working in SSIS

    P.S. It seems that it started to happen with the latest ACE 12.0 update v.14.0.7015.1000


    Wednesday, November 13, 2019 3:09 PM