The For Loop Container in SSIS will repeat the SQL Integration Services tasks for a given number of times until the given condition is False. It is the same as the For Loop in any programming language. In this chapter, we show you the step by step approach to configure the For Loop container in SSIS with example.
Before we start working with the SSIS 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. To do so, First Drag and drop the SSIS For Loop Container into the Control Flow region
Double click on it will open the SSIS 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 the step by step manner. Even if you don’t know C language, it helps you understand the loop concept.
- InitExpression: For loop starts with the initialization statement so, initialization of the counter variable should be done first (For example, @counter = 1 or @i = 1.).
- EvalExpression: The value of the counter variable tested against the condition. If the condition is True, it will execute the For loop container. Otherwise, For loop terminated.
- AssignExpression: This expression will execute after the end of each iteration. It helps to increase or decrease the counter variable as per our requirement.
Under the SSIS For Loop Container General section, Please specify valid and more meaningful Name and Description.
I think I forgot to create a variable that can use in the For Loop. So, let me close the for Loop editor, and right-click on the design will open the context menu. Please select the Variables option.
As you can from the below screenshot, we created a variable called CounterNumber of Integer type and assigned the default value is 0.
Next, open the For Loop Editor, and assign the appropriate values. From the below, For Loop will start at 0, will execute until it reaches to 10, and for each iteration, CounterNumber value incremented by 1.
Click OK to close the SSIS 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.
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 the SQL Tutorial database. Next, we are using the Direct Input as the SQL statement, so click the … button.
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())
Verify the remaining SSIS For Loop Container settings
Next, go to the SSIS For Loop Container 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)
Click OK to finish configuring the Execute SQL Task parameter Mapping. Let us Run the SSIS For Loop container package
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
