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:
|
1 2 3 4 5 6 7 8 9 10 11 |
-- 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
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.
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.
As you can from the bellow screenshot, we created a variable called CounterNumber of Integer type, and assigned the default value as 0.
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.
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.
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.
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.
|
1 2 3 4 5 6 7 8 9 |
-- 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 settings
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)
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
Thank You for Visiting Our Blog
Hi Suresh,
Thanks for posting the useful information.
If possible can you post article about Package and Project parameters in SSIS if possible.
Regards,
Venkat.
please can you post the new tutorial of power BI
Hi ,
As mentioned in the above post we have gone through each and every step of it , But still we are getting the below error:
*****************************************************************************************************
[Execute SQL Task] Error: Executing the query "INSERT INTO [dbo].[For_Loop_SSISPractice] ([Count …" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
*****************************************************************************************************
Also, We were unable to select 'int32' in Parameter Mapping as this datatype was not available in the drop down menu, So instead we have selected 'LONG' .
Kindly suggest what could be the issue ..
Thanks & Regards,
Ravi
I had the same error but make sure you leave a blanc space between the variable and the sign = , <= when you define the InitExpression ,EvalExpression and AssignExpression it should be @CounterNumber = 0 instead of @CounterNumber=0 , @CounterNumber <= 10 instead of @CounterNumber<=10 and @CounterNumber = @CounterNumber + 1 . When correct that run it again and if you get an error , go back to the parameter mapping again and you will see Int32 WILL be in the list and make a change on it and It will work.