By: Dallas Snider | Comments | Related: More > Integration Services Control Flow Transformations
Problem
How can I configure the SQL Server Integration Services For Loop Container? What options are available? How do you establish variables? How can you test the process?
Solution
The SQL Server Integration Services (SSIS) For Loop Container will allow us to repeat a task or tasks x number of times as if we had written a "for loop" in most programming languages. This tip was written using SQL Server 2016 Community Technology Preview 2.0's SQL Server Data Tools in Visual Studio Ultimate 2013. The steps shown here will also work in previous versions of SSIS.
To begin this tip, I used the T-SQL below to create a table that will allow us to watch for changes in our control loop variable.
CREATE TABLE [dbo].[tblForLoopExample]( [LoopExampleKey] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL, ControlLoopVariableValue int, [LastUpdated] [datetime] )
In SSIS, we need to create a variable for our counter. Select Variable from the SSIS menu as shown below.
We will add a variable named Counter with the Int32 data type.
In this tip, we will use the For Loop Container to repeat an Execute SQL Task. Drag an Execute SQL Task onto the Control Flow palette and double-click to display the General page of the Execute SQL Task Editor. Select ADO.NET as the Connection Type and create an ADO.NET connection if one does not already exist.
Then enter the following in the SQL Statement text box:
insert into dbo.tblForLoopExample values (@Counter, getdate())
Click on Parameter Mapping on the left side of the Execute SQL Task Editor to display the Parameter Mapping page of the Execute SQL Task Editor. In the Variable Name column select User::Counter (it should be at or near the bottom of the drop-down list). Select Input as the Direction, Int32 as the Data Type, and enter "Counter" for the Parameter Name. We will leave the Parameter Size at -1. Click on OK when finished.
Before adding the For Loop Container, it is a good programming practice to make sure the code to be repeated in a loop works correctly before invoking the loop.
Running a select query on our test table shows that the INSERT statement worked correctly. Remember that the Counter variable is initialized to zero.
Now we will drag a For Loop Container from the SSIS Toolbox to the Control Flow palette.
Next, we need to drag the Execute SQL Task onto the For Loop Container.
The red circle with white X indicates that we need to configure the For Loop Container. Double-click on the For Loop Container to display the For Loop Editor window. We will set values for the InitExpression, EvalExpression and AssignExpression so the Execute SQL Task executes 10 times.
For the InitExpression, we will initialize the Counter value to zero.
- The EvalExpression needs to evaluate to True or False. For the EvalExpression, we will test to see if the Counter value is less than 10. When the EvalExpression evaluates to False, the for loop will stop.
- The AssignExpression is where we will increment our counter variable. In this tip, we will increment our counter by 1.
- Click on OK to save these configuration changes.
Notice that the red circle with the white X has disappeared. Now it is time to test the For Loop Container. Before testing, I dropped and recreated the test table that was created above.
The package runs successfully.
Querying the table shows that our for loop worked as designed.
Next Steps
The amount to increment and the terminating conditions can be set using variables instead of being hard-coded as we see in this tip.
Check out more tips and tutorials about SSIS on MSSQLTips.com.
- SQL Server Integration Services (SSIS) Tutorial
- Configure the Flat File Source in SQL Server Integration Services 2012 to read CSV files
- Case Statement Functionality in SQL Server Integration Services Derived Column Transformation
- Introduction to the Dimension Processing Destination in SSIS 2012
About the author
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
