Tutorial Gateway

For Loop Container in SSIS

by

The For Loop Container in SSIS will repeat the SQL Integration Services tasks for given number of times, until the given condition is False. This is same as the For Loop in any programming language. In this article we will show you the step by step approach to configure the For Loop in SSIS with example.

Before we start working with the For Loop Container, Let me create an Empty Table in SQL Server:

-- For Loop Container in SSIS Example
USE [SQL Tutorial]
GO

CREATE TABLE [dbo].[SSISForLoopExample](
	[Key] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[CounterNumber] [int] NULL,
	[LoopNumber] [varchar](50) NULL,
	[LastUpdated] [datetime] NULL,
)
GO

For Loop Container in SSIS Configuration

In this example, we will create a variable. Next we are going to increment that variable by 1 using the For Loop, and then save that value in the table that we created above. In order to do so, First Drag and drop the For Loop Container into the Control Flow region as shown below

For Loop Container in SSIS 1

Double click on it will open the For Loop Editor. You can use this editor to configure it. Please refer For Loop in C Programming to understand the programming flow of the For Loop in step by step manner. Even if you don’t know C language, it will help you understand the loop concept.

  • InitExpression: For loop starts with the initialization statement so, initialization of counter variable should done first (For example @counter = 1 or @i = 1.).
  • EvalExpression: The value of the counter variable is tested against the test condition. If the condition is True then it will execute the For loop container. If the condition fails then For loop will be terminated.
  • AssignExpression: This expression is executed after the end of each iteration. It helps to increase or decrease the counter variable as per our requirement.

Under the General section, Please specify valid and more meaningful Name and Description.

For Loop Container in SSIS 2

I think I forgot to create a variable that can be used in the For Loop. So, let me close the for Loop editor, and right-click on the design are will open the context menu. Please select the Variables option as we shown below.

For Loop Container in SSIS 3

As you can from the bellow screenshot, we created a variable called CounterNumber of Integer type, and assigned the default value as 0.

For Loop Container in SSIS 4

Next, open the For Loop Editor, and assign the appropriate values. From the below you can see that, For Loop will start at 0, will executed until it reaches to 10, and for each iteration CounterNumber value will be incremented by 1.

For Loop Container in SSIS 5

Click OK to close the For Loop Container. Let me drag and drop the Execute SQL Task inside the For Loop Container. We are going to use this Execute SQL task to INSERT data into the SQL table.

For Loop Container in SSIS 6

Double click on it will open the Execute SQL Task Editor to configure it. Let me select the Connection Type as ADO.NET Connection, which is connecting to SQL Tutorial database. Next, we are using the Direct Input as the SQL statement so, click the … button to write the custom SQL command.

For Loop Container in SSIS 7

Please write your custom SQL statement here. As you can from the below screenshot, we are writing an SQL Insert statement to insert data into SSIS For Loop Example table.

-- For Loop Container in SSIS Example
INSERT INTO [dbo].[SSISForLoopExample]
           ([CounterNumber]
           ,[LoopNumber]
           ,[LastUpdated])
     VALUES
           (@CounterNumber
           ,'Loop Number' + CAST(@CounterNumber AS VARCHAR(50))
           ,getdate())

For Loop Container in SSIS 8

Verify the remaining settings

For Loop Container in SSIS 9

Next go to the parameter settings to assign the parameter that we used in the SQL Command. As you can see, we assigned the User variable (Variable we are getting from the For Loop) to Parameter Name (this is the name that we used in SQL Statement)

For Loop Container in SSIS 10

Click OK to finish configuring the Execute SQL Task parameter Mapping. Let us Run the SSIS For Loop container package

For Loop Container in SSIS 11

Let me open the SQL Server management studio, and check whether we inserted the records multiple counter values into the destination table using SSIS For Loop container or not

For Loop Container in SSIS 12

Thank You for Visiting Our Blog