ssis excel source connection question

  • 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

  • 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

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

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

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

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