Home > SSIS, SSIS programming > Programming For Loop Container

Programming For Loop Container

June 14th, 2009 Arun Mallick Leave a comment Go to comments

While building an ETL package, sometimes we come across to repeat same types of tasks many times i.e. like to do certain task depending upon the number of students in the class, or any other condition, etc. So instead of developing same containers or tasks so many times, it’s always better to iterate it with certain parameters.

To resolve the above situation, SQL Server Integration Service (SSIS) comes with one special container called “For Loop Container“. This container has the same logic that a “For loop” keyword have in leading programming languages. It will iterate through all the tasks inside it till the condition satisfies. It acts as a repeating control flow. For each iteration of the loop, the For Loop container evaluates an expression that is specified and repeats its workflow until the expression evaluates to False.

Following are the elements used to configure For Loop Container:

  • InitExpression: (Optional) Specifies an expression that initializes variable values used in the loop. This is optional because even if the value is not initialized, it will take the default value assigned to the corresponding variable as its initial value.
  • EvalExpression: This is the terminating condition for the Loop. Specifies an expression that stops the loop when the expression evaluates to False. The resulting value of this expression must be a Boolean value.
  • AssignExpression: (Optional) Specifies an expression that changes a condition in the same way each time the loop iterates. This works like incrementing condition in ‘For’ operators in programming languages, i.e. i++, j–.
  • Expression Pane: Here we have a collection of Expressions. The evaluation result of each expression is assigned to a property and replaces the value of the property. It is basically used to dynamically assign the Expression parameters for the above elements.

The below diagram shows an example for the implementation of FOR LOOP Container in a SSIS package. It has a package variable name @counter which is set to 0 initially. Our objective is to run a task (to insert a record in a given table) five times so that the table will be populated five times with the same record.

Design of Package

So to get this done, the elements of the FOR LOOP Container has to be set as follows:

  • InitExpression: @Counter = 0 [Optional as the value is already initialized to 0]
  • EvalExpression: @Counter < 5
  • AssignExpression @Counter = @Counter + 1

For Loop Container Configuration

A For Loop container can have only one evaluation expression. But it can be nested with other FOR LOOPs as many times, thus can be used to implement complex looping requirements in the package.

The Table structure for the [dbo].[Employee_Dim] table is:

Column_name

Type

employee_Sl_No

int

emp_name

varchar

emp_id

int

emp_DOB

datetime

The “Create Employee Table Task” will create the table named [dbo].[Employee_Dim] in the database, if the table does not exists.

Statement to create table:

/****** Object: Table [dbo].[Employee_Dim] ******/

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[Employee_Dim]‘) AND type in (N‘U’))

CREATE TABLE [dbo].[Employee_Dim](

[employee_Sl_No] [int] IDENTITY(1,1) NOT NULL,

[emp_name] [varchar](50),

[emp_id] [int] NULL,

[emp_DOB] [datetime] NULL CONSTRAINT [DF_Employee_Dim_emp_DOB] DEFAULT (getdate())

) ON [PRIMARY]

Then the For Loop will iterate for 5 times executing Insert Record (Employee Dim). This Execute SQL Task will insert one record to the table [dbo].[Employee_Dim] each time it is executed.

Statement to INSERT record:

INSERT INTO [dbo].[Employee_Dim]

([emp_name]

,[emp_id]

,[emp_DOB])

VALUES

(‘EmpName’ + Cast(? as varchar)

,101

,getdate())

Here, Employee Name is derived from the variable @Counter. So, Employee Name will be different for each iteration.

Setting parameter to INSERT query

After successful execution of the package, the result set would be like this:

Result Set

Let’s do the same by Programming Integration Services. Here, the package will be created first and then an Execute SQL Task named “Create Employee Table Task” will be added to it along with the corresponding Connection Manager named “LocalHost.TesTDB“. After that, the For Loop Container will be added containing the task “Insert Record (Employee Dim)”.

The code below shows the above package programmatically and the code itself is self-descriptive. The code for SQL server 2005 and SQL Server 2008 are a bit different; both the approaches are given below.

Add the below References before developing the code.

Microsoft.SqlServer.Dts.Design
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SQLServer.DTSRuntimeWrap
Microsoft.SQLServer.ManagedDTS

Programming Integration Service in SQL Server 2005

Expand image C# .Net Expand
Expand image VB .Net Expand

Programming Integration Service in SQL Server 2008

Expand image C# .Net Expand
Expand image VB .Net Expand

To make your work more easy and effortless, SQL Lion team comes up with a unique tool named “SSIS Component Explorer” that will help you in retrieving the Creation Name, Component Class ID, etc about any component like Control Flow Tasks, Data Flow Transformations, Connections Managers, Log Providers, etc in SSIS package. It also provides code snippets for each component.
Download the free version from here.
Categories: SSIS, SSIS programming
  1. jayamanjunath
    January 30th, 2010 at 15:09 | #1
    Reply | Quote

    Cast(? as varchar)

    the above said statement is not clear, can u able to expain it clearly,

    which will be helpfull for me

    jayamanjunath ks
    +919035342730

  2. Arun Mallick
    January 30th, 2010 at 15:48 | #2
    Reply | Quote

    Well,
    As in the above statement, I am trying to insert employee’s names for each iteration of the loop, so just to distinguish each employee name, I am concatenating constant value “EmpName” with the counter which is of integer data type and is coming by the parameter “User::Counter”, as it is impossible to concatenate one string value to an integer value, hence I am type casting integer to string before concatenation. Here, “?” is the placeholder for the integer value.
    Thanks,

  1. No trackbacks yet.