Azure Data Factory Pipeline Variables


By:   |   Updated: 2019-08-14   |   Comments (1)   |   Related: More > Azure

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:

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:

variables

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:

invalid

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

Conclusion

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

Next Steps


Last Updated: 2019-08-14


get scripts

next tip button



About the author
MSSQLTips author Fikrat Azizov Fikrat Azizov has been working with SQL Server since 2002. Hes currently working as Senior BI Consultant at BDO Canada.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools