As we know Azure Data Factory (ADF) Version 2 now has the ability to use expressions, parameters and system variables in various components throughout the service. This is brilliant and the best thing about v2 allowing us to build some very dynamic parameter driven pipelines. However, the syntax isn’t always that obvious when you use the expression builder and when trying to include dynamic bits of JSON within some existing JSON string. Microsoft calls this feature string interpolation in the documents page linked below.

https://docs.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions

To explore this further, in this post is one such example of a few problems you may face when adding ADF system variables and pipeline parameters into the body of a REST API call.

Scenario

We create a generic email sender pipeline that can be used throughout our ADF service to produce alerts. Something like this:

The emailer pipeline contains only a single ‘Web’ activity with pipeline parameters for the caller and reported status. The parameters are passed to the API body and used in the email body. The ‘Web’ activity hits a simple Azure Function to perform the email sending via my Office 365 SMTP service. The C# I used for the function can be downloaded from here.

Next, we create a parent pipeline, like the below. Here we have a ‘Lookup’ activity and a ‘ForEach’ activity performing a ‘Copy’ with success or fail conditions to the child email alert pipeline.

Hopefully this pattern is familiar and gives enough context for the point of the post. Moving on

Problems

Here comes the frustration and the main reason for the blog post if you use the ADFv2 portal UI and expression builder to develop. In the child pipeline ‘Web’ activity settings;

• If you create the required JSON for the web activity body in an external tool (eg. VS Code) and copy it directly into the ADFv2 field this will fail. For whatever reason at run time this isn’t treated as dynamic content, even though the syntax is correct.

• If you start with a static set of values in your web activity body, then select to add dynamic content via the expression builder. This will fail. Because the API body is already JSON the expression builder doesn’t handle the string interpolation correctly. If you add the required variable you’ll end up with something like the below where is treats the body JSON as the whole expression. Gif for help:

Solution

My advice, create your required JSON body in an external tool with the correct syntax for the dynamic parts. Then copy and paste it into the expression builder window. Currently if you do anything other than these exact steps you’ll probably get syntax errors in the JSON.

For extra flavour in my final email alert I also added some HTML tags to make the email received a little nicer. My final REST API Body below.

Apologies for the code block as a picture, I couldn’t stop my blog rendering the HTML tags within the message value.

Resulting email:

I hope this was helpful.

Many thanks for reading.

Principal consultant and architect specialising in big data solutions on the Microsoft Azure cloud platform.

Data engineering competencies include Azure Data Factory, Data Lake, Databricks, Stream Analytics, Event Hub, IoT Hub, Functions, Automation, Logic Apps and of course the complete SQL Server business intelligence stack.

Many years’ experience working within healthcare, retail and gaming verticals delivering analytics using industry leading methods and technical design patterns.

STEM ambassador and very active member of the data platform community delivering training and technical sessions at conferences both nationally and internationally.

Father, husband, swimmer, cyclist, runner, blood donor, geek, Lego and Star Wars fan! View all posts by mrpaulandrew