How to make SSIS Read an Excel File?
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.
December 10th, 2011 12:53pm

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
Free Windows Admin Tool Kit Click here and download it now
December 10th, 2011 12:56pm

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...
December 10th, 2011 1:01pm

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
Free Windows Admin Tool Kit Click here and download it now
December 10th, 2011 1:12pm

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 falsehttp://www.rad.pasfu.com
December 10th, 2011 1:15pm

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).
Free Windows Admin Tool Kit Click here and download it now
December 10th, 2011 2:12pm

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!!
December 10th, 2011 2:16pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics