Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ssis excel source connection question Expand / Collapse
Author
Message
Posted Wednesday, January 15, 2014 8:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:54 AM
Points: 158, Visits: 406
in ssis. I need to loop through n number of excel files from a folder. The problem is that each files first tab has a different name. is there a way to specify the first tab as an integer in the connection? for example in the connection tell t to use sheet(0) or something lioke that
Post #1531148
Posted Wednesday, January 15, 2014 9:25 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:10 AM
Points: 607, Visits: 1,163
I think you'd have to do this in a script task and then pass the value into a variable to use later on in a data flow task excel source.

You can get the name of the first sheet in a workbook each time, have a read of this and it should help you.

Best of luck





I'm on LinkedIn
Post #1531185
Posted Tuesday, January 21, 2014 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:54 AM
Points: 158, Visits: 406
to close the loop.

I've been having several issues w/ SSIS. specifically w/ it's file consumption. In this instance u have to access the tab name as opposed to some sort of tab array. Ideally it should allow for tab(0) type thing. Nonetheless, I'm starting to learn that there are several other etl tools that don't seem to have the same issue's I’m running into w/ SSIS.

As far as this specific issue. I ended up writing a C# console app to step through all folders and files and consume all excel spreadsheets into db table. For whatever reason I don’t appear to have the same sorts of missing column issues due to data inconsistencies as I do in ssis.
Post #1533164
Posted Wednesday, January 22, 2014 12:51 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:04 AM
Points: 488, Visits: 1,993
Another option might be to have PowerShell output the contents of your Excel files as .CSV files before processing. If I recall correctly, you don't need to name the 'tab' of a CSV in connection strings.
Post #1533803
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse