Synapse snack - Get child pipeline value to parent

Case

We can pass values from the parent pipeline to the child pipeline via parameters, but how do we get return values from the child pipeline to the parent pipeline?

Pipeline return value

Solution
In the past we used a Webhook activity to call a child pipeline via the Rest API and then we can use a Web activity in the child pipeline to return a value via the callbackuri. This workaround was way to complex and you end up with to different executions that are not related to each other.

Last month Microsoft introduced for both Synapse Worksapce and Azure Data Factory, the new Pipeline return value option in the Set variable activity. This allows you to return one or more HARDCODED values to the child pipeline.

1) Child pipeline - Set Variable

First we need to create a child pipe that we will be calling from a parant pipeline in the next step. The only required activity is the Set Variable activity

• So first create a new pipeline. We called it PL_Child
• Add a Set Variable activity to the canvas of your child pipeline. Ours is called Return Value
• In the Settings tab of the activity set the Variable type to Pipeline return value (preview)
• Now you can create a new string variable with a hardcoded return value. For expressions you need to change the type to for example Expression. Note that compared to pipeline variables you have way more types to choose from.

Return value

Note that this is not an existing pipeline variable so other activities cannot change its value. Also note that the Value field cannot be overruled with an expression. So only hardcode values.

2) Parent pipeline - Execute Pipeline

Now we need the parent pipeline that will be calling the child pipeline to get the return value.

• Create a new pipeline. Ours is called PL_Parent
• Add a Execute Pipeline activity to the canvas of your new pipeline
• Set it to execute the Child pipeline of step 1
• Make sure the Wait on completion is checked!

Execute Pipeline to retrieve return value

Note that if you execute this parent pipeline that you won't see the return value in the Output window. This means we need an other activity to see that return value.

Output with no visible return value

3) Get return value

To get the return value you can use an expression with a pipelineReturnValue after the output of your Execute Pipeline activity followed by the name of your return value: @activity('EPL_GetAnswer').output.pipelineReturnValue.MyAnswer

In this example we will store the return value in a pipeline variable. Note that if you used anything else than String, Boolean or Array that you need to add a type conversion in the expression:

@string(activity('EPL_GetAnswer').output.pipelineReturnValue.MyAnswer)

Read return value from output

Output showing return value

Note that you cannot have multiple Set Variable activities running and all returning values (even if they use different names). Then it will only return one of those causing an error in your expression. You can have mulitple Set Variable activities returning values if you put them in for example an if construction so that only one will run.

Conclusions

In this little snack you learn about the new preview(!) feature in ADF and Synapase to return values from the child to the parent pipeline. A very nice new feature that we were waiting for al long time, it could use some small improvements like the expressions (why do we need an expression type?) and an option to see the output of the Execute pipeline activity.