locked
How to make SSIS Read an Excel File? RRS feed

  • Question

  • I’ve been trying for a while now (won’t say how long), to get BIDS to read a very simple Excel file.  I’m talking SIMPLE!!  No matter what I don, SSIS keeps throwing an error, and of course it doesn’t say what the error is, so I can’t really debug it.  I’ve tried this at least 20x with flat files, and it works perfect each time.  I’ve done Data Conversions, Sorting, Union All, and several other Transformations; all work perfect.  Trying to used Excel as a data source, is proving to be a mind numbing experience.  Of over 20 different attempts, it hasn’t worked even once.  I can make it as simple as you can possibly imagine, and SSIS, refuses to even perform the first step (I’m not even trying to create a table in SQL Server anymore). 

    I have the Excel file path (very simple)

    I have the Excel version (very simple)

    I have the Connection Manager (very simple)

    I have the Sheet name (very simple)

     

    All I can do is see a preview of the Excel sheet before the process runs.  As soon as I het F5, I get an instant error, for no reason whatsoever, and no debugging options, whatsoever.

     

    Is there some secret trick I need to know about? 

     

    I can't show a snap shot, because MSFT won't allow pictures to be loaded now.

     

     

    Wednesday, December 7, 2011 5:53 PM

Answers

  • seems you have 64bit problem

     

    if you run the package in BIDS, go to solution explorer, right click on project, select properties

    go to debugging tab,

    and set Run64BitRuntime to false


    http://www.rad.pasfu.com
    • Marked as answer by ryguy72 Wednesday, December 7, 2011 7:18 PM
    Wednesday, December 7, 2011 6:15 PM

All replies

  • do you get any error message? if yes put it here.

    also you can configure error output on the source and redirect error rows to a flat file destination to troubleshoot them.


    http://www.rad.pasfu.com
    Wednesday, December 7, 2011 5:56 PM
  • There is no error message; only a red box named 'Excel Source'.  That's the only way I can tell it doesn't work.  Well, I can see there is no new table in SServer too.  Otherwise, it's just a black box.

    In VBA, .NET, etc., you can use debugging tools to troubleshoot.  I don't know of any debugging tools in SSIS...

    • Edited by ryguy72 Wednesday, December 7, 2011 6:02 PM
    Wednesday, December 7, 2011 6:01 PM
  • didn't you see any error message in progress/execution results tab?
    http://www.rad.pasfu.com
    Wednesday, December 7, 2011 6:03 PM
  • I have this from the Output window:

    SSIS package "Package.dtsx" starting.
    Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
    Error: 0xC00F9304 at Package, Connection manager "Excel Connection Manager 1": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
    Error: 0xC020801C at Data Flow Task, Excel Source [114]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Excel Source" (114) failed validation and returned error code 0xC020801C.
    Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
    Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
    SSIS package "Package.dtsx" finished: Failure.

     

    I have this from the Progress window:

    some stuff . . . doesn't seem very relevant

    [Excel Source [114]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

     

    [Connection manager "Excel Connection Manager 1"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

    some stuff . . . doesn't seem very relevant

    Wednesday, December 7, 2011 6:12 PM
  • seems you have 64bit problem

     

    if you run the package in BIDS, go to solution explorer, right click on project, select properties

    go to debugging tab,

    and set Run64BitRuntime to false


    http://www.rad.pasfu.com
    • Marked as answer by ryguy72 Wednesday, December 7, 2011 7:18 PM
    Wednesday, December 7, 2011 6:15 PM
  • is this a *.xls file or a *.xlsx ?

     


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Wednesday, December 7, 2011 7:11 PM
  • Reza!  You are my hero!  That was exactly it!!  Other than someone like you, who in the heck is ever going to figure that out???

     

    Thanks so much!!

    • Edited by ryguy72 Wednesday, December 7, 2011 7:19 PM
    Wednesday, December 7, 2011 7:16 PM
  • Very good helped me. Reza is a hero.
    View 500 videos on .NET , ASP.NET , SQL Server from http://www.youtube.com/dnfvideo
    Friday, February 3, 2012 8:55 AM

  • you are a life savior.... thanks a lot !!


    Sunday, November 25, 2012 7:06 AM
  • This fixed it for me. Thanks!
    Friday, December 21, 2012 6:48 PM
  • I do not see what you are talking about. I right click on my package in solution explorer then select properties. I have only two fields. I do not see a debugging tab or the set Run64Runtime

    [Connection manager "EANC_excel_Path"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

    I know it is a 64bit issue but is there a fix? Am I over looking something?

    Tuesday, April 28, 2015 4:26 PM

  • Tuesday, April 28, 2015 6:43 PM
  • excellent!!! thanks! 
    Wednesday, May 6, 2015 2:04 PM
  • I have the same error but my Run64BitRuntime is already set to false.  Is there something else I need to check.
    Wednesday, February 10, 2016 10:28 PM