System Variables in Azure Data Factory: Your Everyday Toolbox

Rayis Imayev, 2018-12-04 (first published: 2018-11-20)

(2018-Nov-20) After working and testing the functionality of variables within Azure Data Factory pipelines, I realized that it’s worth to explore existing system variables. That’s basically could be my toolbox to collect and store control flow metrics of my pipelines.

Looking at the official Microsoft resource System variables supported by Azure Data Factory you’re given with a modest selection of system variables that you can analyze and use both on a pipeline and pipeline trigger level. Currently, you have three ways to monitor Azure Data Factory: visually, with the help of Azure Monitor or using a code to retrieve those metrics.

But here is a case of how I want to monitor a control flow of my pipeline in Azure Data Factory:

This the same data ingestion pipeline from my previous blog post – Story of combining things together that builds a list of files from a Blob storage and then data from those files are copied to a SQL database in Azure. My intention is to collect and store event information of all the completed tasks, such as Get Metadata and Copy Data.

Here is a current list of pipeline system variable in my disposal:
@pipeline().DataFactory – Name of the data factory the pipeline run is running within
@pipeline().Pipeline – Name of the pipeline
@pipeline().RunId – ID of the specific pipeline run
@pipeline().TriggerType – Type of the trigger that invoked the pipeline (Manual, Scheduler)
@pipeline().TriggerId – ID of the trigger that invokes the pipeline
@pipeline().TriggerName – Name of the trigger that invokes the pipeline
@pipeline().TriggerTime – Time when the trigger that invoked the pipeline. The trigger time is the actual fired time, not the scheduled time.

And after digging a bit more and testing pipeline activity, I’ve discovered additional metrics that I can retrieve on the level of each individual task:

Here is my final pipeline in ADF that can populate all these metrics into my custom logging database table:

And this is how I made it work:

1) First I created dbo.adf_pipeline_log table in my SQL database in Azure:

2) Then I used [Append Variable] Activity task as “On Completion” outcome from the “Get Metadata” activity with the following expression to populate a new array type var_logging variable:

var_logging = 
@concat('Metadata Store 01|Copy|',
,activity('Metadata Store 01').Duration,'|'
,activity('Metadata Store 01').Error,'|'
,activity('Metadata Store 01').ExecutionDetails,'|'
,activity('Metadata Store 01').ExecutionEndTime,'|'
,activity('Metadata Store 01').ExecutionStartTime,'|'
,activity('Metadata Store 01').JobId,'|'
,activity('Metadata Store 01').Output,'|'
,activity('Metadata Store 01').ActivityRunId,'|'
,activity('Metadata Store 01').Status,'|'
,activity('Metadata Store 01').StatusCode)

where each of the system variables is concatenated and separated with pipe character “|“. 

I did a similar thing to populate the very same var_logging variable in the ForEach container where actual data copy operation occurs:

3) And then I used this final tasks to populate my dbo.adf_pipeline_log table using data from the var_logging variable by calling a stored procedure:

Where the whole trick is to split each of the text lines of the var_logging variable into another array of values split by “|” characters. Then by knowing the position of each individual system variables values, I can set them to their appropriate stored procedure parameters / columns in my logging table (e.g. @split(item(),’|’)[0] for the ActivityTask).

This provided me a flexibility to see both Completed and Failed activity runs (to test a failed activity I had to temporarily rename the target table of my Copy Data task). I can now read this data and get more additional insights from the SQL Server table. 

Let me know what you think about this, and have a happy data adventure!





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads