Return values from other pipelines in Synapse Pipelines

There is a new variable type in town - pipeline return value!

Introduction

In Azure Synapse Analytics¹, the "Set Variable" activity allows you to create and assign values to variables that can be used within your data pipeline. These variables can be used to store values such as connection strings, folder paths, or configuration parameters.

The Set Variable activity is typically used within a pipeline to store intermediate values that can be used later in the pipeline. For example, you could use a Set Variable activity to capture the results of a database query, then pass the results to another activity that uses the results for further processing.

Variables created using the Set Variable activity are only accessible within the pipeline where they are defined. You cannot access them from other pipelines or activities. Until now!

Create new variable values

Inside the settings there is now an option to choose between two kind of variable types, pipeline variable and pipeline return value. The latter one let’s you pass on values to parent pipeline. Let’s try it out.

Here we can define multiple values in one activity. You need to specify name, type and value. In this example we are testing the string and expression type. However, there are more types such as object, int, float, boolean and null.

Here we define two variables that we would like to return.

When we run the pipeline we get the values in the output of the activity.

Returning values.

Run parent pipeline

In order to test the output of the childPipeline we create a parentPipeline with an Execute pipeline activity. However, running the pipeline do not give the expected result. Where are the variable values that we defined?

Shouldn’t it be a set of variables in the output?

In turns out that in order to get these values you will need to reference the pipelineReturnValue field from the output activity node in the expression builder. Then we can reference the values by their name from the child pipeline. There is no intellisense on this node so you will have to remember the name and you are likely to break the reference if you change variable name in child pipeline! Be careful.

Use pipelineReturnValue to reference defined variable values.

Now let’s assign this expression to a variable to test the result. In this pipeline we would like to return the expression value ChildExpression. Hit debug and Voila!

Conclusion

The Set Variable activity is an important tool for creating dynamic and reusable pipelines in Azure Synapse Analytics, and it enables you to create complex workflows that can be adapted to meet changing business requirements.

The new variable type broadens the scope beyond a single pipeline and opens up a whole new set of opportunities to tie your orchestration together.

How will you use this new feature?

1. and Azure Data Factory..