Hello,

I have an excel file which contains lots of sheets. Some of them are named as DW-<day>-<month> (for e.g; DW-1-July). Like this I have sheets for the whole month. I have other sheets too with a different name. I would like to import data from these sheets only (DW ones). Upon my research I have found that this can be achieved via For Each Loop Container (I guess!). 

Post data import, I have a set of T-SQL query that I plan to execute via Execute SQL Task. 

Please guide me. Thanks in advance.

-Rajiv

http://www.techbrothersit.com/2013/12/ssis-read-multiple-sheets-from-excel.html

In the FELC, you will have to add dummy script task(ST). Add precedence constraint with expression between ST and DFT. The expression should check for DW sheets only. This way FELC will only load desired sheets to SQL table.

Maybe this.

http://www.singhvikash.in/2012/11/ssis-how-to-loop-through-multiple-excel.html

Or something from one of these.

http://sqlage.blogspot.in/2013/12/ssis-read-multiple-sheets-from-excel.html

http://beyondrelational.com/modules/24/syndicated/398/Posts/18163/ssis-how-to-loop-through-multiple-excel-sheets-and-load-them-into-a-sql-table.aspx

http://www.codeproject.com/Tips/395541/How-to-load-data-from-multiple-Excel-sheets-to-any

http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

Hi Harry,

I added a Script Task and then Data Flow Task inside FELC. Connected them with the green arrows. Upon editing the green arrow, I selected "Expression and Constraint" in evaluation and operation, value as success, all constraints must evaluate to True. I have a variable as: varSheetName, String type and value as DW$.

What expression to add that would cause the constraint to check for DW sheets only.

Please assist. 

-Rajiv

• Edited by RajivIR 22 hours 4 minutes ago

You need to compare the FELC variable's value to the DW. When both match, control should flow down to the DFT else iterate over the next.

Hi Rajiv,

Based on your current scenario, we can add an expression like below in the Precedence Constraint (supposing the variable in the Variable Mapping tab of Foreach Loop Editor is named Sheetname):
LEFT(@[User::Sheetname], 3)== "DW-"

Then it will only import data from the sheets which begin with "DW-" for the sheet name.

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong