SSIS Looping through Excel Sheets

Question

I am using SSIS2012, I am trying to import about 25 excel files (each containing about 70(variable) sheets) into SQLserver2008.

I have built it so that it will loop through all the excel sheets and import the first sheet, but this is useless, how can I loop all the excel files and loop all of the sheet names into SQL?

I have set up a script task to get the sheetName into a variable, but I don't know what to do from there.

Is my question clear enough?

I am much more fluent in VB over C# so if you're using script task, ideally paste VB,net code.

Thanks, James.

Answer 1

I've done a similar thing. What I did was add a Foreach Loop Container, and set enumerator property to Foreach File Enumerator. Retrieve the file path and store in a variable. Then use that variable to dynamically set the file connection using the property extensions editor.

Finally, put your data flow inside the Foreach Loop Container.

Doing this I was able to import data for each Excel file found in the directory specified.

Answer 2

You can Loop through Excel Files and Tables by Using a Foreach Loop Container

Here you will use nested foreach loops in the Control Flow. These will loop first over the files, and then loop over the tables within the files (worksheets). Inside the loops you will have a Data Flow with an Excel File Source.