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.
Welcome to the Blog of Paul Andrew 
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
LikeLike
Hello Matthew, you must sue the @mark in from of your jeeson string and remove all line breaks and spaces and @ chars inside your json, also remove the internal curly braces around activity()…. prays this helps!
LikeLike
How to do that? I’ve been going round in circles trying to solve this.
LikeLike
Hi Matt,
Did you get a chance to fix this, I am at exactly similar situation, escape character is getting added to the double quotes and hence API call is failing.
Thanks in advance,
Nikesh
LikeLike
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
LikeLike
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
LikeLike
Thanks for your work in filling in many azure documentation gaps and this interesting blog post. I am trying to do something similar and should be very simple as a pipeline web activity creating a message in the storage queue. The storage queue requires an access key and I am using the url as described in the microsoft documentation (https://.queue.core.windows.net/). I am using both the ‘Authorization’ and ‘Content-Type’ headers and am not getting any errors related to authorization. However I get this vague error message in return in which I am not sure how to respond.
{
“errorCode”: “2108”,
“message”: “Error calling the endpoint. Response status code: “,
“failureType”: “UserError”,
“target”: “Web1”
}
Any suggestions or pointers would be appreciated.
LikeLike
I am trying to use GetMetadata activity and used Childitems to get all child folder and file details.
and I have assigned the returned array to variable of type array. And now I want to check for each file type weather it is folder or file and use that in If condition activity to perform certain activity based on type = ‘Folder’, or Type = ‘File’ .
Can you please help me achieve this how to access the variable array value in If condtion boolean Expression
LikeLike
Expression used @contains(variables(‘FileList’),’Folder’) but its not returning correct value
LikeLike
Hey.
can you help me on this .
I am trying to create a web activity request where my input is azure sql db table .
i have created a linked linked service and given authorization .
while i am running the pipeline i am unable to get the output data since the body should have json response. where my input is table.
So how to convert my input table into json and then create a dynamic content on the body
LikeLike
I could make it to work with dynamic parameters like this:
@json(concat(‘{“PIPELINEID”: “‘,pipeline().parameters.ParentPipelineID,'”,”ENVIRONMENT”:”‘,pipeline().parameters.Environment,'”,”REFRESHTYPE”:”‘,pipeline().parameters.RefreshType,'”}’))
LikeLike
I am trying to setup Web activity to POST file from Azure Data Lake Gen 1 to REST API service, have used Dataset and Linked service for file reference and then parameterized dataset ‘
@pipeline().parameters.filename’. Sample Curl request which i am trying to achieve
curl –location –request POST ‘https://xxxx/files’ \
–header ‘X-API-TOKEN: xxxx’ \
–form ‘file=xxxxx’
error ‘Missing file’. As the body is mandatory for POST request, i tried to pass it as file = @pipeline().parameters.filename but did not work. I am starting to wonder is it possible to achieve with data factory to POST a file to REST API service, could you assist
LikeLike
It’s certainly possible. If you don’t want to use the body, just add {} into the value box.
LikeLike
Hi Venk AV
Did you get this going? I am trying to call Salesforce Bulk Job API and want to post CSV file from ADLS gen 2. My filename is stored in a file name, how can I post CSV file data in API is what I am not able to figure out.
Can you put the details here from your web activity as a reply.
LikeLike
Thanks for the wonderful article. Saved me lot of time.
LikeLike