Unfortunately, as of April 2022 there is not an option to parameterise or add dynamic content to an “Execute Pipeline” activity to invoke a pipeline run.
However, with the use of a Microsoft API there is method which we can use to overcome this.
In this blog post we’ll use the “Pipeline – Create Pipeline Run” API documented below:
https://docs.microsoft.com/en-us/rest/api/synapse/data-plane/pipeline/create-pipeline-run
Using this API will allow us to dynamically pass in a pipeline name and trigger it. It will also us to dynamically pass in parameters to the called pipelines.
For this example, we’ll create 3 simple pipelines:
- “Wait 5 Seconds”
- “Wait 10 Seconds”
- “Wait 15 Seconds”
Plus a “Control Pipeline” that we’ll get onto later.
Each of the “Wait” pipelines contain a single “Wait” activity and a parameter called “WaitDuration” with a “Type” of “Int”. The idea being that we will dynamically pass in a wait duration from an array.
The “wait time in seconds” option in the settings tab of the “Wait” activity is then parameterised to refer to the parameter as shown below.
Next, we’ll build the “Control Pipeline” based on a “For Each” activity which will loop around the array displayed in notepad++ below. This array references the Pipeline Name that we want to dynamically trigger plus the Wait Duration we want it to wait for.
To use this array we’ll create a “ParameterArray” parameter with “Type” equal to “Array” in the “Control Pipeline”. We’ll set the default value equal to the array from above.
Next, within the settings tab of the “ForEach” activity we have the option of ticking the sequential option and listing the items we want to loop over.
Here we’ve got the “Sequential” option ticked. This just means that it will loop through each item in our array, starting with the very first item. For the “Items” option we’ll add the dynamic content which refers to our “ParameterArray” parameter.
Next, within the “ForEach” activity we’ll create a “Web” activity with the following options.
URL:
https://{Your Synapse Workspace Name}.dev.azuresynapse.net/pipelines/@{item().PipelineName}/createRun?api-version=2020-12-01
For example:
https://mytestsynapseworkspace.dev.azuresynapse.net/pipelines/@{item().PipelineName}/createRun?api-version=2020-12-01
*Notice here that I’ve added @{item().PipelineName} into the string which is our pipeline name from our “ParameterArray” parameter. This is what will make our Web API POST request dynamic.
Method:
POST
Headers:
Name : Content-Type
Value : application/json
Body:
{“WaitDuration”:@{item().WaitDuration}}
*Also notice here that i’ve added @{item().WaitDuration} into the string which is our wait duration from our “ParameterArray” parameter for each item. This allows us to feed each of the pipelines with the wait duration.
Integration runtime:
AutoResolveIntegrationRuntime
Authentication:
System Assigned Managed Identity
Resource:
https://dev.azuresynapse.net/
Once we’ve entered all of these details we should have a “Web” activity which looks similar to the image below:
Now let’s trigger our “Control Pipeline”:
As we can see each our 3 pipelines have been triggered by just triggering the “Control Pipeline”. We can also see that the respective parameters have been passed in successfully to each of the triggered pipelines.
Wait 5 Seconds:
Wait 10 Seconds:
Wait 15 Seconds:
Note, if you’re using Azure DevOps Git CI/CD integration you’ll need to make sure that the pipelines you want to execute already exist in live mode for the API request to recognise that they exist. If not you’ll get an error code suggesting that the pipeline doesn’t exist.
Also note that there may be some Azure permissions that you’ll need to configure with your Azure Administrator in the Azure portal to allow this to work.
In summary, this method is a trigger and forget approach. There is no consideration in the for each activity to wait for the pipeline to either succeed, fail or timeout. However, stay tuned for my next blog where I’ll show you how to wait for the pipeline to complete before moving on to the next one in your for each loop!
Link for that blog to follow soon!
What’s the purpose of three child pipelines which only differ by the wait time when you’re passing wait time as a parameter? Wouldn’t one parent pipeline with one child pipeline generate the same result?
Hi Jordan. You’re absolutely correct, one parent pipeline with one child pipeline would generate the same result. This example was more to demonstrate that you could execute “different” pipelines dynamically using metadata. For example, one pipeline with a simple copy data activity and another pipeline with a dataflow.
Hi ! great article but i m struggling with types of permission i need to give, any idea ?