Learn SSIS : For Each loop

Welcome back..Today I am going to cover ForEach loop.
ForEach loop we use when we don’t know the number of iteration loop, lets take an example…
The scenario is in adventure works database we have a view vEmployeeDepartment, so what we are going to do is we will be generating files by department name.
Let’s start..
The package will look like this…






















The execute sql task will get list of departments from the view, and the foreach loop will loop over the department list one by one and generate the files for each department.


1. Execute SQL Task: To get list of departments from the view

2. ForEach Loop: Loop over all the departments we got from Execute SQL task

3. Script Task: to form a T-SQL, which fetches department wise records from table

Here is the code that needs to be entered into Script task, its nothing but variable assignment.

public void Main()

        {

            // TODO: Add your code here

            Dts.Variables["varSQL"].Value = "Select EmployeeID,FirstName,MiddleName,LastName,JobTitle,StartDate from HumanResources.vEmployeeDepartment  where Department = '" + Dts.Variables["varDepartment"].Value.ToString() + "'";

            //MessageBox.Show(Dts.Variables["varDepartment"].Value.ToString());

            Dts.TaskResult = (int)ScriptResults.Success;

        }

4. Dataflow Task: Execute the T-SQL we formed in above step and generate the files for each department.

5.Expressions: To set destination file name dynamically, we use expressions, which will change the connectionstring of destination connection.

6. Variables: We are using following three variables
      a. varDepartments: This holds department list we got from Execute SQL task
      b. varDepartment: This holds department name for every iteration of ForEach loop
      c. varSQL: Holds the T-SQL which we form in SCRIPT task

Well guys that‘s it, run the package and see the results at the destination directory, files of each department will be generated as shown…