Introduction

We can use iteration activities to perform specific tasks multiple times. This helps to save time and minimize errors with the pipeline design process. Azure Data Factory provides several iterations and conditional activities.

In this article, we will discuss the below activities:

• Filter
• ForEach
• If Condition
• Switch
• Until

Filter Activity

We can use the Filter activity to filter data to an input array. This is what the main properties for this activity look like:

I created an array parameter that has three values 100,200 and 300:

Let's see the settings tab in Filter activity:

• Items - Here I added pipeline parameter PAR_ARR.
• Condition - Added expression to return items that are greater than 150.

Let's run the pipeline. Now in the output window, we can only see the filtered values, 200 and 300. The value 100 is ignored by the filter activity as it is less than 150:

ForEach Activity

The Foreach activity is used to iterate over a collection specified in the activity. Within the Foreach activity, we can specify one or more activities that will execute on each iteration. The activity looks like the image below:

I created an array parameter to use in ForEach activity with three values:

In the settings, we have these options:

• Sequential - If this option is unchecked then for loop executes sequentially otherwise parallelly (default value is false). For example, if we have 5 inner activities in the Foreach activity, all 5 activities will execute at once if the Sequential option is unchecked.
• Batch count - This determines the number of parallel execution when the Sequential option is unchecked. The default value is 20 and the maximum value is 50.
• Items - This must be an array expression.

Let's add the parameter in the expression of the item:

I added a stored procedure activity inside the Foreach activity:

I created a table to store the array value from the pipeline :

CREATE TABLE [dbo].[TBL_ForEach_Value](
[VALUE] [int] NULL
) ON [PRIMARY]
GO

I used the below procedure to insert array values from the pipeline to the SQL table :

CREATE PROCEDURE [dbo].[sp_insert_foreach_activity] @VALUE INT
AS  
INSERT INTO TBL_ForEach_Value (VALUE)
VALUES (@VALUE)

Let's edit the stored procedure activity, I added the stored procedure name and the parameter. In the parameter value, I added @item which is Foreach iterator, and provides an array value in each iteration:

Let's run the pipeline. As expected in the output window below, we see the stored procedure name thrice because the Foreach activity iterated three times. This is because the pipeline array has three elements:

Now, let's validate the output from the SQL table. We can see all three array values now inserted into the table:

If Condition Activity

The If Condition activity takes an input expression, which evaluates to either true or false, based on that a set of activities executes in either True block or False block:

I added a variable Var of String type in the pipeline, with the default value of Red :

Now I specified the variable in the expression below. The activity will compare the variable value with the value "Red". As both values are the same the expression evaluates to true:

I created a table to store values from inner activity:

CREATE TABLE [dbo].[TBL_If_Activity_Value](
[VALUE] VARCHAR(10) NULL
) ON [PRIMARY]
GO

I used the below procedure to insert the inner activity value to the table:

CREATE PROCEDURE [dbo].[sp_insert_if_activity] @VALUE VARCHAR(10)
AS  
INSERT INTO TBL_If_Activity_Value (VALUE)
VALUES (@VALUE)

This is the stored procedure activity in True block and I used value "True" in the procedure parameter :

This is the stored procedure activity in the False block and here I used "False" in the procedure parameter :

Now in the variable, the value is Red, and the If expression is shown here:

@variables('Var').value=="Red"

The expression will return True and the True activities will execute.

Let's run the pipeline, Note that The stored procedure in true activity is executed:

We will check the result from the SQL table and we can see the value 'True' inserted into the table:

Now changed the expression value to compare the variable value with 'Green' so that expression evaluates to false:

Now I run the pipeline, false activity is executed:

Let's check the value from the table and we can see the value 'False' inserted into the table:

Switch Activity

The Switch Activity takes an input expression then evaluates multiple case statements:

Let's create a parameter in the pipeline:

The Switch activity has these options:

• Expressions - We need to provide expressions here and based on it different case statements will execute. The expressions string value is case-sensitive.
• Add case - Here we can add multiple cases statements, and each case statement can have one or more activities. There is a default case available, if no case is matching then the default case will execute.

I added the parameter in the expressions:

Now I added two cases Red and Green. Each case has one activity within it:

I created the below table to store values from Case activities :

CREATE TABLE [dbo].[TBL_Switch_Activity_Value](
[VALUE] [varchar](10) NULL
) ON [PRIMARY]
GO

I used the below procedure to insert values from the Case activities :

CREATE PROCEDURE [dbo].[sp_insert_swith_activity] @VALUE VARCHAR(10)
AS  
INSERT INTO TBL_Switch_Activity_Value (VALUE)
VALUES (@VALUE)

I added the stored procedure in the default block and the value "Default" used in the parameter :

In the Red block also I added the stored procedure and the value "Red" used in the parameter :

I added the stored procedure in the Green block and the value "Green" used in the parameter :

Let's run the pipeline, as expected activities in Red block activity executed because the parameter value was "Red":

Also, we can see the value "Red" is inserted into the SQL table:

Now I changed the parameter value to "Green" so that Green block activity executes:

Now I run the pipeline and Green block activity executed:

The value "Green" inserted into the SQL table by the stored procedure activity :

Now, I changed the parameter value to "Yellow" so that the default block executes:

Let's run the pipeline and this time default block activity excuted :

Also, I checked the default value from the SQL table :

Until Activity

The Until activity executes activities within it until the expressions evaluate to true. We can also specify a timeout value to the Until activity.

In the Until activity settings, these options are available:

• Expression - This must return true or false.
• Timeout - Optionally we can specify a timeout value. This value accepts a string value in the d.hh:mm:ss or hh:mm:ss formats. The default timeout is 7 days and the maximum timeout value is 90 days.

Let's create a parameter in the pipeline:

I added this expression

 @equals(pipeline().parameters.PAR_COLOUR,'RED')

in the properties below:

I added a lookup activity in the Activities section:

Let's run the pipeline :

I changed the parameter value so that expression evaluates to false :

Now, I modified the timeout value to 10 seconds :

Let's run the pipeline, this time inner lookup activity is executed multiple times until time out value reached :

Conclusion

In this article, we discussed various iteration and conditional activities available in Azure Data Factory.