none
Using Data access mode: "Table of View"; I NO longer can access the Sheet Name; With an "Excel Source" or "OLE DB Source" RRS feed

  • Question

  • After many hours of trouble shooting I am still do not understand how this is not working when it has worked in the past and with many SSIS packages that I have in place.

    Using either a "Excel Source" or "OLE DB Source"Connection; I am NO longer able to reference or bring the listing of "Sheet Names" when selecting "Data Access Mode" for "Table or View". I have tried Numerous fixes that have been listed with NO success.

    On the Excel Connection side side I was using with success: Microsoft Excel 2007-2010 but only works in Sql Command mode.

    Microsoft Excel 2013 does work in either "Table View" or "SQL Command Mode"

    The Only action that I can use on the 2 connections above is with a "Data Access Mode" of SQL Command; this would mean that I will need to change a lot of packages that are in place or hope they still work.

    ( example: select * from [R1$A1:IZ1000] )

    Here is my environment Configuration:

    Visual Studio Professional 2017, Version 15.9.11

    Reading Excel(xlsx) files using Microsoft Office Standard 2013

    Both 32bit and 64bit Microsoft Data Access Components have been applied to my environment, and based on the KBs that I have tried; no success when using "Table or View".

    I have tried numerous parameters which were applied to the connections but no success.

    At this point I looking for some assistance and solutions to resolve the issue.

    Many thanks from a frustrated developer....! 


    Error Message:
    "Could not retrieve the table information for the connection manager 'Excel Connection Manager'.
    Object reference not set to an instance of an object."


    Thursday, October 3, 2019 7:02 PM

All replies

  • Hi Jdelafuente,

    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

    Thursday, October 3, 2019 7:29 PM
  • I will have peak at the details and you are correct on the details...!

    Thanks

    Thursday, October 3, 2019 8:11 PM
  • Yitzhak,

    I reviewed your details and that is also the fix that I came up with, have you seen anything that suggest a possible fix...?

    I have my environment configured with same details and a few parameter details at the connection level.

    I will dig them up and share...

    If I figure anything out I will pass it on...


    jdelafuente

    Thursday, October 3, 2019 8:46 PM
  • Hi Jdelafuente,

    To me it is a bug. Somebody needs to open an official ticket with Microsoft to resolve it.

    For right now, we have a workaround.


    Thursday, October 3, 2019 9:23 PM
  • Hi Jdelafuente,

    You could give your feedback in this link.

    Regards,

    Zoe


    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

    Friday, October 4, 2019 6:47 AM
  • Hello Zoe,

    The issue that I reported which is being experienced by others is still an issue with no resolution. The work around at the moment appears to work but the behavior of the component and the driver has changed or has been impacted by another change.

    The latest ACE 12.0 update v.14.0.7015.1000; is NOT performing as it did when we have developed and deployed MANY packages to production.

    In short the Excel TABS cannot be viewed when using the driver and the only reference to an excel spreadsheet at this time; is with a SQL command reference.

    The test is pretty easy to create by creating a New data Flow; use an Excel Source; create your Excel Connection; Reference the excel connection in the Excel Source Component; Select Table or View and issue is encountered.

    Result of test below:

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

    Thanks...


    jdelafuente

    Monday, October 7, 2019 2:27 PM
  • Hi Jdelafuente,

    As Yitzhak said before, it maybe a bug, try the workaround first.

    Or you could give some feedback to Microsoft.

    Regards,

    Zoe


    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

    Tuesday, October 8, 2019 1:22 AM