Azure Data Factory Pipeline Variables

By:   |   Comments (1)   |   Related: > Azure Data Factory


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.


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:

explore variables

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:

factory resources

Open the Variables tab and select the variable SimpleVar we created earlier, from the variable name drop-down list:

explore variables

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:

add dynamic content

Finally, let's publish the changes, trigger this pipeline manually, and switch to the ADF monitor screen to see execution results:

custom range

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:

all pipeline runs

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:

factory resources

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):

append variable

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:

factory resources

Let's execute this pipeline again and examine the input values for ArrayVar variable:

activity run


Pipeline variables allow storing temporary values within a pipeline and using them subsequently and that they resemble SSIS variables.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Fikrat Azizov Fikrat Azizov has been working with SQL Server since 2002 and has earned two MCSE certifications. Hes currently working as a Solutions Architect at Slalom Canada.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, September 25, 2019 - 3:41:17 PM - Phil Parkin Back To Top (82569)

In the screenshot, the value of ArrayVar appears as 'Sample value 2'. What happened to 'Sample value 1'?

get free sql tips
agree to terms