SSIS

How to execute the foreach loop fixed number of times in SSIS

In this post i will expain how to execute the foreach loop fixed number of times in SSIS. In other words i can say how to restrict or run the foreach loop so that it get executed limited number of times

Situation : I want to execute the Data Flow Task in the Foreach Loop container 3 times only. Before Data Flow Task , Introduce a Script Task as shown in the image below.Also create a variable named count of datatype Int32 with default value 0.

 

foreach loop ssis restrict execution

 

Double click on script task and set ReadWriteVariables property as User::count and then click on Edit Script as shown in the image the below.

 

foreach loop ssis run

 

After clicking on Edit Script in Script Task Editor , a new scripting window will open where you can write C# code.In the new scripting window, under void Main() function add the code highlighted as shown in the image below.This code is incrementing the User::count variable by 1 whenever the foreach loop will be executed.Save the code in the scripting window and close the scripting window. After that click OK to Script Task Editor.

 

C sharp script ssis

 

Now right click on the Precedence Constraint (green line) between Script Task and Data Flow Task and click on Edit as shown in the image below.

 

execute the foreach loop limited number of times in SSIS
Precendence Constraint Editor Dialog Box will open. Set the Evaluation Operation to Expression and also set the Expression which make will check whether User::count value is less than 3 or more than 3 as shown in the image below.

 

setting up precedence constraint ssis

 

If the User::count value is more than three then expression will be False and Data Flow Task will not be executed as shown below.

 restrict execution

Leave a Reply