Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Configure the SQL Server Integration Services For Loop Container


By:   |   Read Comments   |   Related Tips: More > Integration Services Control Flow Transformations

Attend a SQL Server Conference for FREE >> click to learn more


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.

Select Variable

We will add a variable named Counter with the Int32 data type.

Add an integer variable named Counter

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.

Execute SQL Task Editor General page

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.

Execute SQL Task Editor Parameter Mapping page

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.

Execute Package

Running a select query on our test table shows that the INSERT statement worked correctly. Remember that the Counter variable is initialized to zero.

Verify contents in the table.

Now we will drag a For Loop Container from the SSIS Toolbox to the Control Flow palette.

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.

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 Loop Container needs configuration

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.
For Loop Container has been configured

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.

Ready to test

The package runs successfully.

The package runs successfully

Querying the table shows that our for loop worked as designed.

Query shows the loop executed 10 times.
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.



Last Update:


signup button

next tip button



About the author
MSSQLTips author Dallas Snider 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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools