ssis excel source connection question

Question

ssis excel source connection question

Snargables

SSCrazy Eights

Points: 8855
More actions
January 15, 2014 at 8:32 am

#302845

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17463 More actions · Answer 1

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

Snargables SSCrazy Eights Points: 8855 More actions · Answer 2

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.

sqldriver SSChampion Points: 10440 More actions · Answer 3

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.