Looping through Files using ForEach Loop container in SSIS 2008 R2

Friends,

In this post we are gonna discuss about Looping through Files using ForEach Loop container in SSIS 2008 R2. For each loop container is used to iterate through collection of objects and in this case we are gonna discuss about looping through each file in the given path.

In te below example we are gonna loop through the files and shown the path of the file in a popup message but in the pace of popup we can write different functionality like moving the files to different folders and so on.

Step 1: Click on Start —-> Programs —–> Microsoft SQL Server 2008 —-> SQL Server Business Intelligence Development Studio

Step 2: Now On below screen Go to File —-> New —-> Project

Step 3: By clicking Project we will get below window. In this window select Integration Services Project and give a proper name to solution and project. Then click OK.

Step 4: You will get following screen. As you can see, it has four tabs Control Flow, Data Flow, Event Handlers and Package Explorer.

Step 5: While staying on Control Flow tab, from toolbox side menu drag and drop For Each Loop Container on Control Flow Pane.

Step 6: Following same step drag Script Task (to popup the path) inside the For Each Loop Container. Name these two tasks with some relevant name. After doing this your screen will look like below. I have given Loop through Files name to For each loop container and Script task to Show file name.

Step 7: Right click on For Each Loop Container select EDIT. Below screen will appear, fill in the required properties:

Below is the list of properties and values to be supplied

Enumerator: For each File Enumerator
Folder: Give full path of folder in which we need to iterate.
Files: *.pdf (this will iterate through all pdf files)
Retrieve file name: Fully qualified (will return full path)

Step 8: Now go to Variable Mappings. Create a new variable as shown on screen. Then click OK. The path of each file will be assigned to this variable while looping.

Step 9: Now right click on Script Task/Show File Name. Select Edit and whenScript Task editor wondow pop up select Edit Script Button

Step 10: Clicking on Edit script will open visual studio code the inside main method we will right code to show a message box with file name in it.

public void Main()

{

MessageBox.Show(Dts.Variables[“FileName”].Value.ToString());

// TODO: Add your code here

Dts.TaskResult = (int)ScriptResults.Success;

}

In ReadOnlyVariables give name of variable which you created to hold file name. In this case it is User::FileName.

Then click OK and return to our package.

Step 11: Now run the package and you will get to sc below result.

This message box is showing iterating through pdf files and showing full path and name.

This was the basic use of For each loop container control flow task. This task can be used in very useful scenarios.

Regards,
Roopesh Babu V