Tips and Tricks for SQL-BI: Foreach Loop Container in SSIS

The Foreach Loop Container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages but in a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

I have already mentioned about Foreach Loop Enumerators in my previous article. Now I'll explain Foreach Loop Container using Foreach File enumerator. I'll move data of multiple excel sheets into a database table.

1. To begin, I have created three Excel Spreadsheets and store these sheets at location D:\SSIS\Hari\DataFiles.
These files have sample data for my example. Below is thesnapshot of file 2010-06-20.xlsx:

2. Open existing project or create new project using BIDS. I will use existing project Sample saved at location D:\SSIS\Hari\Sample (which I have used for other articles as well). Right click on SSIS Packages folder in Solution Explorer and click New SSIS Package. Rename it with ForeachLoopContainer.dtsx as shown below:

3. Drag and drop Foreach Loop Container from Toolbox. Add a package variable FileName of String type and assign default value D:\SSIS\Hari\DataFiles\2010-06-20.xlsx. I'll use this variable to store File Name of each iteration.

4. Drag and drop Data Flow Task inside Foreach Loop Container and rename it with DFT - Load Data From Excel.

5. Double click the Foreach Loop container to open Foreach Loop Editor to configure the Foreach Loop container. Click on Collection and provide the folder that contains the files to enumerate (in our example D:\SSIS\Hari\Sample), specify a filter for the file name and type *.xlsx and specify the fully qualified file name as retrieve file name as shown below:

6. Click on Variable Mappings and Select variable User::FileName from dropdown box. Corresponding Index for this variable will be 0. Click OK to proceed and save changes.

7. Add Excel Connection Manager to read data from Excel sheets:- Right click on Connection Managers area and select New Connection... and select EXCEL as type and click on Add button. Enter D:\SSIS\Hari\DataFiles\2010-06-20.xlsx in Excel File Path and make sure that checkbox First row has column names is checked. Click OK to proceed and save changes.

8. Config ConnectionString with FileName variable:- Click on Excel Connection Manager --> go to properties --> Click on Expression --> This will open Property Expression Editor. Select ExcelFilePath from Property dropdown box. Click on Expression and Drag and drop User::FileName from Variables. Click OK two times and save changes.

9. Add OLEDB connection manager to load data into database table: Right click on Connection Managers area and select New OLEDB Connection... This will open Configure OLE DB Connection Manager. Click on New button. Enter Server name and database name in respective boxes as shown below:

10. Double click on Data Flow Task to go to Data Flow tab. Drag and drop Excel Source from Data Flow Sources and double click on it to open Excel Source Editor. Select Excel Connection Manager in OLEDB connection manager and Sheet1$ in Name of the Excel sheet dropdown boxes.

11. Click on Columns in left side window. This will display all the External Columns available in excel and Output Columns. Click OK to process and save changes.

12. Drag and drop OLEDB Destination from Data Flow Destinations. Click on Excel Source to see green and red output lines. Connect green output line to OLEDB Destination. Now double click on OLEDB Destination to open OLE DB Destination Editor. Select local.Test as OLE DB connection manager. Click on New... button to create Destination table. It will open Create Table wizard. You can edit T-SQL statement as per your need. You can change the name of table if you wish. Click OK to proceed.

13. Click on Mappings to map source and destination columns. It will automatically map the columns by their matching names. However you can map the columns manually as well. Click OK to process and save changes.

14. We are done with package development. Finally package looks one as shown below:

Now its time to execute the package and check the result.

Go to package location D:\SSIS\Hari\Sample\SSIS-Sample1 --> Right Click on package ForeachLoopContainer.dtsx and click SQL Server 2008 Integration Services Package Execution Utility. This will open Package Execute Utility wizard.