SQLServerCentral Article

Using Pipeline Return Value in Azure Data Factory and Azure Synapse Analytics



In this article, we will talk about Azure Data Factory’s newly introduced Pipeline return value features. We can return value from a pipeline using the Set Variable Activity and retrieve the value in another pipeline using the Execute pipeline activity. Let's create a demo pipeline to explain this feature.

Pipeline Demo

I used a Get Metadata activity in the pipeline. The get metadata activity is used to retrieve metadata information from Azure such as file name, size, etc. I added two arguments in the settings:

  • Size – It returns the size of the file.
  • Item name – It returns the file name.

I used a flat file in the dataset file path:

Now I added a Set Variable activity in the pipeline and selected the pipeline return value option. I added two return variables as below:

  • Var_File_Name – It will return the name of the file, the type option is Expression and the value is @activity('Get Metadata1').output.itemName.
  • Var_File_Size – It will return the size of the file, the type option is Expression and the value is @activity('Get Metadata1').output.size.

I have added another pipeline, named PL_Parent, which will be acting as a parent pipeline. In the parent pipeline added an executed pipeline activity and selected PL_CHILD in the invoked pipeline option:

Let’s create a table to store the return values from the child pipeline. I created the below table to store FILE_NAME AND FILE_SIZE:

I used a stored procedure to insert values to the table TBL_File_History:

Now, let's add a Stored Procedure activity and assign above created stored procedure SP_FILE_DATA_INSERT. It has two parameters as below:

  • FILE_NAME: values '@activity('Execute Pipeline1').output.pipelineReturnValue.Var_File_Name'
  • FILE_SIZE: values '@activity('Execute Pipeline1').output.pipelineReturnValue.Var_File_Name'

Let’s run the pipeline. As expected, the file name and file size are available now in the table:



In this article, we discussed how we can return values from one pipeline to another pipeline using the pipeline return value option in the Set Variable activity. It is a useful feature in parent-child pipeline design patterns.


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating