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

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

In the calling pipeline-child pipeline paradigm, you can use the Set Variable activity to return values from the child pipeline to the calling pipeline. In the following scenario, we have a child pipeline through Execute Pipeline Activity. And we want to retrieve information from the child pipeline, to then be used in the calling pipeline.

Introduce pipeline return value, a dictionary of key value pairs, that allows communications between child pipelines and parent pipeline.

Prerequisite - Calling a Child Pipeline

As a prerequisite, your design needs an Execute Pipeline Activity calling a child pipeline, with Wait on Completion enabled on the activity.

Configure Pipeline Return Value in Child Pipeline

We expanded the Set Variable activity to include system variables Pipeline Return Value. You don't need to define them at pipeline level (as opposed to any other variables you use in the pipeline).

1. Search for Set Variable in the pipeline Activities pane, and drag a Set Variable activity to the pipeline canvas.
2. Select the Set Variable activity on the canvas if it isn't already selected, and then its Variables tab, to edit its details.
3. Choose Pipeline return value for variable type.
4. Select New to add a new key value pair.
5. The number of key-value pairs that can be added is only limited by the size limit of the returned JSON (4 MB).

There are a few options for value types, including

Type Name	Description
String	A constant string value. for example: 'ADF is awesome'
Expression	It allows you to reference output from previous activities. You can use string interpolation here to include in-line expression values such as "The value is @{guid()}".
Array	It expects an array of string values. Press "enter" key to separate values in the array
Boolean	True or False
Null	Signal place holder status; the value is constant null
Int	A numerical value of integer type. For example: 42
Float	A numerical value of float type. For example: 2.71828
Object	Warning complicated use cases only. It allows you to embed a list of key value pairs type for the value

Value of object type is defined as follows:

[{"key": "myKey1", "value": {"type": "String", "content": "hello world"}}, 
 {"key": "myKey2", "value": {"type": "String", "content": "hi"}}
]

Retrieving Value in Calling Pipeline

The pipeline return value of the child pipeline becomes the activity output of the Execute Pipeline Activity. You can retrieve the information with @activity('Execute Pipeline1').output.pipelineReturnValue.keyName. The use case is limitless. For instance, you can use

• An int value from child pipeline to define the wait period for a wait activity.
• A string value to define the URL for the Web activity.
• An expression value payload for a script activity for logging purposes.

There are two noticeable callouts in referencing the pipeline return values.

1. With Object type, you can further expand into the nested json object, such as @activity('Execute Pipeline1').output.pipelineReturnValue.keyName.nextLevelKey
2. With Array type, you can specify the index in the list, with @activity('Execute Pipeline1').output.pipelineReturnValue.keyName[0]. The number is zero indexed, meaning that it starts with 0.

Note

Please make sure that the keyName you are referencing exists in your child pipeline. The ADF expression builder can not confirm the referential check for you. The Pipeline will fail if the key referenced is missing in the payload

Special Considerations

• While you can include multiple Set Pipeline Return Value activities in a pipeline, it is important to ensure that only one of them is executed in the pipeline.

  

  To avoid the previously described missing key problem when the calling pipeline, we encourage you to have the same list of keys for all branches in child pipeline. Consider using null types for keys that don't have values, in a specific branch.

• The Azure Data Factory expression language does not directly support inline JSON objects. Instead, it's necessary to concatenate strings and expressions properly.

  For example, for the following JSON expression:

  {
    "datetime": "@{utcnow()}",
    "date": "@{substring(utcnow(),0,10)}",
    "year": "@{substring(utcnow(),0,4)}",
    "month": "@{substring(utcnow(),5,2)}",
    "day": "@{substring(utcnow(),8,2)}"
  }
  

  An equivalent Azure Data Factory expression would be:

  @{
    concat(
      '{',
      '"datetime": "', utcnow(), '", ',
      '"date": "', substring(utcnow(),0,10), '", ',
      '"year": "', substring(utcnow(),0,4), '", ',
      '"month": "', substring(utcnow(),5,2), '", ',
      '"day": "', substring(utcnow(),8,2), '"',
      '}'
    )
  }
  

Related content

Learn about another related control flow activity:

• Set Variable Activity
• Append Variable Activity