Problem
In these series of tips, I am going to explore Azure Data Factory (ADF), compare its features against SQL Server Integration Services (SSIS) and show how to use it towards real-life data integration problems. In previous posts, we have used pipeline and dataset parameters, which allow setting their internal properties by external objects-triggers. Pipeline parameters are similar to SSIS package parameters, which also need to be set from outside of these packages. Data integration scenarios sometimes require storing certain values within the data flow and using them subsequently inside the same flow. SSIS packages have variable functionality, which allows assigning static values, as well as expressions to internal variables. ADF pipelines also have the variable concept, which we will be exploring in this post.
Solution
Creating Azure Data Factory Pipeline Variables
The process of creating ADF pipeline variables is similar to creating parameters. However, unlike parameters, ADF variables can only have three data types:
- String
- Boolean
- Array
To explore variables, I have created a new pipeline, named ExploreVariables_PL. Adding pipeline variables is a simple, straightforward process. Let’s create a simple variable of type string, following the below steps:
Select pipeline ExploreVariables_PL, open Variables tab, click the New button, assign the name (SimpleVar in this example) and leave the data type as String:
Now that we have created a variable, we can use activities to assign the values to this variable as follows.
Expand the General category on the Activities panel, drag-drop Set Variable activity to the central panel:
Open the Variables tab and select the variable SimpleVar we created earlier, from the variable name drop-down list:
Next, we need to assign the value to this variable. A variable can have a static string value or dynamic expression. To add a dynamic expression, click the Add dynamic content link under the Value text box:
The Add Dynamic Content window allows building dynamic expressions interactively, using available system variables and functions. In this exercise, we’ll use two system variables (‘Pipeline name’ and ‘Pipeline run ID’) and the concat function to concatenate these variables. To do that, scroll-down, expand String Functions under Functions category and click the concat function, which will result in adding a new expression in the expression window:
Next, place the cursor in the concat function, select variable Pipeline Name under System Variables category, type a comma, then select the Pipeline run ID variable. Here’s the screen with the final expression:
Finally, let’s publish the changes, trigger this pipeline manually, and switch to the ADF monitor screen to see execution results:
Form this screen we can open activity run details using the View Activity Runs button under the Actions column and use the Input or Output button to see details:
As you can see from the above screen, the value field contains the concatenation of the pipeline name and run ID, which is what we expected.
Adding Array Type Variables in Azure Data Factory
Now that we learned how to add a pipeline variable and assign a value, we can consider a slightly more complex case of adding array type variables. As the name suggests, this type of variable can contain more than one value, which could be useful in creating iterative logic.
The process of adding an array variable is similar to adding a simple variable, see the below screenshot with new array type variable ArrayVar:
Assigning new values to the array variable can be achieved using the Append Variable activity. Let’s drag-drop a new activity of type Append Variable into the central pipeline panel, open the Variables tab of that activity, select variable ArrayVar we created earlier from the Name drop-down list and assign a static string value (‘Sample value 1’ in the below example):
Obviously, we can assign more than one value to this array variable, so let’s add another Append Variable activity to the central panel, with a similar configuration, except we’ll assign the value ‘Sample value 2’ to it. Here is final screen with all three activities we created so far:
Let’s execute this pipeline again and examine the input values for ArrayVar variable:
Conclusion
Pipeline variables allow storing temporary values within a pipeline and using them subsequently and that they resemble SSIS variables.
Next Steps
