ADFv2 Variable String Interpolation – REST API Body

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.

3 thoughts on “ADFv2 Variable String Interpolation – REST API Body

  1. Hey Paul,
    Thanks for the post I am having this exact issue using web activities in data factory (I think), however I’m still having issues. When you say “copy and paste it into the expression builder window”, do you mean the “Add Dynamic Content” window that pulls out? I am currently adding in this simple body:
    {
    “source”:”test”,
    “textContent”:”@{activity().output.value}”
    }
    since the url I am posting too requires “source” and “textContent” in the body of the json. However, I keep getting the error: “Error calling the endpoint”. This might not be exactly the problem you were having, but it seems my issue may be related to the json converting strange since this is what the body above looks like in the request:
    “body”: “{\”source\”:\”test\”,”textContent\”:\”\”}”

    If you have any idea of this issue I’m having, I would greatly appreciate any advice!
    Thanks again,
    Matt

    Like

  2. I want to create an activity which will sent mail on status of azure data factory pipeline. In this if any activity failed then mail must trigger and if nothing failed then send success mail.

    Pleas help me on this

    Like

    1. Hi, thanks for the comment. In your situation you will need to use two separate activities to send the email. Each triggered using the ADF depends on feature for pass/fail. Hope this helps. Paul

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.