none
Excel Source stopped working in SSIS RRS feed

  • Question

  • Hi all, since few days Excel Source in SSIS stopped working due to the following message

    Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
    Failed to connect to the source using the connection manager 'Excel Connection Manager'


    I troubleshooted for a long time and already tried several solutions found:

    - set to false the Run64bitRuntime option for the project

    - set the Excel version to 97-2003, 2007-2010, 2016 in Excel Connection Manager

    - installed Microsoft Access Database Engine 2010 Redistributable

    - installed Microsoft Access Database Engine 2016 Redistributable

    - ran a repair of Office 2016 installation

    - ran a repair of Microsoft Visual Studio 2017

    I'm opening a new topic for this issue because none of the above attempts worked for me

    The issue started about 5 or 6 days ago

    I have no more ideas. Any suggestion will be appreciated

    Thanks a lot


    Wednesday, September 18, 2019 10:50 AM

Answers

  • Hi CarloDB,

    Many folks started to complain about it recently. It seems like a bug in MS software.

    As a pre-requisite, a 32-bit Microsoft.ACE.OLEDB.12.0 (or 15.0 or 16.0) Provider needs to be installed on dev. machine with Visual Studio (VS).

    You need to do 2 things in Visual Studio:

    • Set the SSIS project in 32-bit mode via Run64BitRuntime to false.
    • In Excel Source Adapter, specify SQL statement instead of referring to Excel's sheet name only:
      - SELECT * FROM [SheetName$A1:B3], by specifying a certain range
      - SELECT * FROM [SheetName$], whole sheet

    P.S. It seems that it started to happen with the latest ACE 12.0 update v.14.0.7015.1000
    Monday, September 23, 2019 5:02 PM

All replies

  • See if this helps

    https://stackoverflow.com/questions/23896901/ssis-excel-connection-manager-failed-to-connect-to-the-source


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 18, 2019 11:01 AM
  • Hi all, since few days Excel Source in SSIS stopped working due to the following message

    Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
    Failed to connect to the source using the connection manager 'Excel Connection Manager'


    I troubleshooted for a long time and already tried several solutions found:

    - set to false the Run64bitRuntime option for the project

    - set the Excel version to 97-2003, 2007-2010, 2016 in Excel Connection Manager

    - installed Microsoft Access Database Engine 2010 Redistributable

    - installed Microsoft Access Database Engine 2016 Redistributable

    - ran a repair of Office 2016 installation

    - ran a repair of Microsoft Visual Studio 2017

    I'm opening a new topic for this issue because none of the above attempts worked for me

    The issue started about 5 or 6 days ago

    I have no more ideas. Any suggestion will be appreciated

    Thanks a lot


    What change did you do recently?

    Did you apply a patch or changed version?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, September 18, 2019 11:54 AM
  • Hello,

    thanks for your reply but as said, I've already done all suggested in this thread with no luck

    Regards

    Wednesday, September 18, 2019 12:07 PM
  • Hello,

    no change or patch applied recently

    Thanks and regards

    Wednesday, September 18, 2019 12:08 PM
  • Hi CarloDB,

    Please try to install both the 32-bit and the 64-bit ACE driver.

    The following link will be helpful:

    Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing.

    Best Regards,

    Mona


    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

    Thursday, September 19, 2019 1:38 AM
  • Hi CarloDB,

    Please remember to click "Mark as Answer" the responses that resolved your issue.

    This can be beneficial to other community members reading this thread. 

    Thank you.

    Regards,

    Mona



    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, September 23, 2019 9:24 AM
  • Hello,

    unfortunately I've already tried this solution but it didn't work.

    Anyway, I've managed to uninstall and reinstall Visual Studio 2017. Just installed, the issue was not present anymore but, when I've reinstalled also Microsoft Analysis Service extension the excel source stopped work again

    Any ideas?

    Thanks a lot

    Monday, September 23, 2019 3:36 PM
  • Hi CarloDB,

    Many folks started to complain about it recently. It seems like a bug in MS software.

    As a pre-requisite, a 32-bit Microsoft.ACE.OLEDB.12.0 (or 15.0 or 16.0) Provider needs to be installed on dev. machine with Visual Studio (VS).

    You need to do 2 things in Visual Studio:

    • Set the SSIS project in 32-bit mode via Run64BitRuntime to false.
    • In Excel Source Adapter, specify SQL statement instead of referring to Excel's sheet name only:
      - SELECT * FROM [SheetName$A1:B3], by specifying a certain range
      - SELECT * FROM [SheetName$], whole sheet

    P.S. It seems that it started to happen with the latest ACE 12.0 update v.14.0.7015.1000
    Monday, September 23, 2019 5:02 PM
  • Hello,

    that worked for me!

    Thank you very much for your help!!!

    Regards

    Tuesday, September 24, 2019 2:06 PM
  • Thank you very much for this workaround!

    I've spent hours finding a solution for this problem.

    @Microsoft

    Why is there no official communication regarding this problem? This needs to be fixed ASAP.

    Tuesday, October 8, 2019 6:16 AM
  • Hello Thanks for the workaround
    I was really frustrated spending hours looking for the problem.

    They should take a look for ETL process. Things doing in 4 or 5 steps, are now needed to be doing in 6 steeps at least for me when control and data flow for exporting multiple tables from any database to excel files.

    Wednesday, October 9, 2019 12:49 PM
  • I agree, I do not understand why Microsoft or their development staff have not stepped up to the plate.

    From a product testing stand point it does not look like they tested the change before they release the change.

    Only work around at this point is to use a SQL command line reference.


    jdelafuente

    Thursday, October 10, 2019 4:37 PM
  • Same was the case with me as well. To test my luck, I installed Visual Studio Community Edition 2019 and added IS,AS extensions and tried to work with Excel Source, it is showing up sheet names without us having to type command.

    Friday, October 11, 2019 10:30 AM
  • Apologies, issue started happening again, even in Community Edition. Hence, opting for "command" would be a viable option.
    Friday, October 11, 2019 11:06 AM