Warning: is_writable() [function.is-writable]: open_basedir restriction in effect. File(/f5/sqllion/public/wp-content/uploads/shareaholic/) is not within the allowed path(s): (/fs3d/sqllion/:/nfsn/apps/php53/lib/php/:/nfsn/apps/php5/lib/php/:/nfsn/apps/php/lib/php/) in /fs3d/sqllion/public/wp-content/plugins/sexybookmarks/sexy-bookmarks.php on line 335
Programming For Loop Container | SQL Lion
Home > SSIS, SSIS programming > Programming For Loop Container

Programming For Loop Container

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

    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. January 30th, 2010 at 15:48 | #2

    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,

  3. Rohidas
    July 28th, 2010 at 12:20 | #3

    VALUES
    (‘EmpName’ + Cast(? as varchar)
    ,101
    ,getdate())

    Please explain ? sign or which int in this place or compete above statement

  4. Rohidas
    July 28th, 2010 at 12:22 | #4

    VALUES
    (‘EmpName’ + Cast(? as varchar)
    ,101
    ,getdate())

    Please explain ? sign or compete above statement

  5. July 28th, 2010 at 12:35 | #5

    Hi Rohidas,
    as you can see above, i am using a “Forloop Container” to insert data into the employee table.

    In the statement,
    INSERT INTO [dbo].[Employee_Dim]
    ([emp_name]
    ,[emp_id]
    ,[emp_DOB])
    VALUES
    (‘EmpName’ + Cast(? as varchar)
    ,101
    ,getdate())

    In the statement, instead of inserting same name for each iteration (in EmployeeName column), i concatenate the value of “@Counter” with the employee name, and “@Counter” value is passed by a parameter in the above “Execute SQL Task” and hence “?” is the placeholder for that argument.

    Let me know, in case your doubt is not clear.
    Thanks,
    Arun

  6. julia
    November 16th, 2011 at 01:51 | #6

    may be good idea,
    but bad example steps,
    I never know what code put what place-task,

    Statement to create table:
    ?
    for Statement to INSERT record
    ?
    it’s messy, confusion, bad,
    ‘This Execute SQL Task will insert one record ‘
    whitch task? I have to guess
    ‘Add the below References before developing the code.’
    where and what file to put these code
    ????

  7. Shiva
    March 7th, 2012 at 15:29 | #7

    I have a select query with date that I want to use for loop query to iterate each time in specific period (for example in 5 months) how can i use this query and set the parameters? because my query has 2 parameters date between date.

    I have a select query that to execute in specific time (date form…. to…. )
    how can i use for loop container with 2 parameter?
    I appreciate your help.

  8. Shiva
    March 7th, 2012 at 16:07 | #8

    @Shiva

    Shiva :
    I have a select query that to execute in specific time (date form…. to…. )
    how can i use for loop container with 2 parameter?
    I appreciate your help.

  1. August 17th, 2011 at 10:58 | #1
  2. August 17th, 2011 at 11:01 | #2
You must be logged in to post a comment.