Azure Data Factory: Increment Variable using Set Variable Activity

We discussed how to load Filename list into SQL table using the Get Metadata activity in the previous post. In today’s post, let’s have a look at the Set Variable activity (and its use case). The Set Variable activity, as the name implies, is used to assign a value to an existing variable in Data Factory. To use this activity, we must create a variable first. Once the variable has been created, we can select the variable in the Name property under the Variables tab of the activity.

Azure Data Factory: Set Variable Activity

Once the variable has been selected, a value text box will appear where the value of the variable can be assigned.

Azure Data Factory: Set Variable Activity properties

Please note, Data Factory supports three variable types, namely, String, Boolean and Array. For the value to be assigned correctly to the variable, it should be compatible with the Variable type.

Now let’s discuss one of the most common use cases for variables. Variables can be used as iterators to manage a for each or Until loop in the control flow of the Pipeline.

Let’s assume we have a variable ‘i’, that we would like to use at the iterator variable.

To implement this, we would need to increment the value of a variable after every iteration. You may think, we could simply use Set Variable activity to perform the equivalent of:

 i = i+1

But Set Variable activity has a limitation that prevents us from referring to the same variable name (which is being assigned) within the activity. To work around this, Microsoft suggests that we break this down into two steps.

Step 1: We create a temporary variable ‘j’ (that will store the incremented value), increment the variable ‘i’ using the code below and assign the incremented value to the temp variable ‘j’ using a Set Variable activity.

@string(add(int(variables('i')),1))

Step 2: We then create a new Set Variable activity, link the output of the first Set Variable activity (from Step 1) to the new Set Variable activity and assign the variable ‘i’ , the value of the variable ‘j’ using the code below.