Foreach Loop container

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. 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.

Step 1:

Click Windows button and go to SQL Server 2008 R2 and run as a administrator.

It shows  a bellow window.

Step 2:

Go to a views and create a new project ( Short cut of New Project is Ctrl + Shift + N).

Step 3:

Give a project name (e.g. IS) and click OK button and then shows a bellow window.

Step 4: 
Drag & drop for each loop from toolbox into the control flow and edit it

By clicking edit option button we get for each loop editor window.

In that editor to the left side of that page we had some options. In that option click on Collection button we get inner page.

In that inner page we had Enumerator option in that click on enumerator we had to select which type of enumerator we want based on file type.(Eg: .txt, excel files etc) for excel file we have to select Foreach ADO.NET Schema Rowset Enumerator

Step 5:

In that page next we had to select connection. Click on connection button. we get new connect click on new connection.

By clicking new connection we get another page as Configure ADO.NET connection manager.

In that page click on NEW we get another page as Connection manager

In that page it had different options. In that 1^st we have to select the provider.  Click on provider in that we get different options. In those select .Net providers for OLEDB.

Again some different Options will come in that select Microsoft  office 12.0 access database Engine OLE DB provider.

 Another option is Server or file name. In that we have to get the file path from the source  where the file is present. Eg C:\ENTERTAINMENT\New Folder\emp d1.xlsx

Then click on all OK buttons. After that we got again the same main page as.

In that we have to select schema in that different  Options will come. In that select Tables.

Step 6:

Next in that Foreach loop editor we have to select variable mapping. In that dff. Options will be there. We have to select variable. Before selecting variable we had to create variables.  

First come to main page then give right click on it we had different Options in that select variables.

By selecting variables we got a dialog box. In that select Add variable.

By selecting add variable we got dff. options as Name, Scope, Data Type, Value.

In that options we give name as any thing in this I am giving Eg As Foreach,

Scope package1, Data type string, Value sheet1$. (sheet1$ is the name of the sheet in excel sheet of emp det1.xlsx.)

Step 7:

After this save this file. Again come back to main control flow. And give right click on it we get dff. Options click on edit button.

In that click on variable mapping.

In that page click on variable we get the name as we had given before as Foreach

After that give index value as 2.

Step 8:
We can come back to control flow. Then in the toolbox from the control flow items drag & drop Data flow task into the for each loop container.

Then click on data flow task. We get data flow.

Step 9:
Drag & drop Excel source in to the Data flow and edit it

In this page OLEDB connection manager. In this click on new. It generate excel connection manager.

In this browse the excel file path. We can browse the file from the source. Then select the file and click on open.

In that Data access mode select table name or view name variable.

After connection manager next click on columns.

Step 10:

Drag and drop  Data conversion .After click on edit button.

In that options we made changes in output Alias as dc_empid, dc_ empname, data type as Four –byte signed integer, string & in length as 50 as per the Eg we had taken before. It’s not a mandatory for all types of Eg.

Step 11:
Drag and drop OLEDB destination and edit it

Give OLEDB connection and in Data access mode select TABLE or VIEW

Step 12:

Click on new button .It will generate again a page as Create Table

create table sreeharsha007

(

         empid float,

         empname nvarchar(255)

) 

Next click on mapping. Already mapping is completed automatically.

Step 13:
Execute task

Step 14:
Go to SSMS and view a table as sreeharsha007

Step 15:

FOR EACH LOOP OUTER CONTAINER PROCESS FOR EXCEL FILES

Take another for each loop container and drag & drop into the control flow

Step 16:

In this page give right click in that several options will be present. In that click on variables.

We get new options in that give name as harsha007, Scope package1, and data type String, Value the file path which is present in the folder Eg. C:\ENTERTAINMENT\New Folder\emp d1.xlsx

Step 17:

Then again go back to main page & give right click on outer for each loop then options will come in that click on edit options.

In that click on variable mapping. In that we had variable & index. In the variable give the variable as given harsha007 & index value as 0.

Step 18:

At below of the page connection managers tab is present. In that file path & file connection manager path will be present. In that 1^st right click on file path & in that different options will rise in that click on properties.

By clicking properties, properties page will arise in that click on Expressions

By clicking Expressions we get property expression editor.

In that property taken as server name & click on expression. By clicking on expression another page will appear as expression builder

In that variables will be present. In that click on variables in that harsha007 is taken & drag & drop in expression.Then again click on Evaluate expression.

Step 19:

Then click on all OK buttons & come back to main page. Next give right click on excel connection manager in that click on properties page.

Step 19:
Execute task

Step 20:
Click on execute button we get the result as present in the Excel sheets we had taken i.e., the old excel sheets & the remaining excel sheets present in the folders.