ADF V2 - Web POST method using Dynamic Content and Variable

Question

Very short version

How do I include an ADF Variable inside a JSON POST request, in a Web Activity within ADF? I feel like this should be a very simple string concatenation, but i can't get it to work

Detail

We have a requirement to run a query / SProc from within ADF, which will return a string containing an error message. That string is to then be passed via the Web Activity in ADF to a Logic App, in order to fire off an email, containing the error.

The setup of the logic app is copied from here:

https://www.mssqltips.com/sqlservertip/5718/azure-data-factory-pipeline-email-notification--part-1/

and then here (part 2)

https://www.mssqltips.com/sqlservertip/5962/send-notifications-from-an-azure-data-factory-pipeline--part-2/

In ADF, I used the Lookup activity, to run a query, which brings back the error (appears to work, the preview returns the correct string) Then I use the Set Variable activity, to take the output of the lookup and store it in a variable.

Last Step is to fire off the POST using the Web Activity.

With this code (tweaked slightly to remove personal details) in my Web Activity, everything works fine and I receive an email

{
   "DataFactoryName": "@{pipeline().DataFactory}",
   "PipelineName": "@{pipeline().Pipeline}",
   "Subject": "Pipeline finished!",
   "ErrorMessage": "Everything is okey-dokey!",
   "EmailTo": "[email protected]"
}

But any attempt to put the contents of the Variable into the Subject part has failed.

This (for example) sends me an email with the subject literally being @variables('EmailSubject')

{
   "DataFactoryName": "@{pipeline().DataFactory}",
   "PipelineName": "@{pipeline().Pipeline}",
   "Subject": "@variables('EmailSubject')",
   "ErrorMessage": "Everything is okey-dokey!",
   "EmailTo": "[email protected]"
}

But I've also attempted various other solutions that result in errors or the email subject just containing the literal thing that I put in there (e.g. + @variables('EmailSubject') +).

I also tried storing the entire JSON in the Variable, and then having the Web activity use only the variable, that returned no errors, but also did not send an email.

This attempt:

{
   "DataFactoryName": "@{pipeline().DataFactory}",
   "PipelineName": "@{pipeline().Pipeline}",
   "Subject": "@{variables('EmailSubject')}",
   "ErrorMessage": "Everything is okey-dokey!",
   "EmailTo": "[email protected]"
}   

Resulted in this input into the web activity - which actually includes the text of the error, which is a bonus ... (text = Job Duration Warning):

{
    "url": "https://azureLogicAppsSiteHere",
    "method": "POST",
    "headers": {
        "Content-Type": "application/json"
    },
    "body": "{\n   \"DataFactoryName\": \"DFNAMEHERE\",\n   \"PipelineName\": \"pipeline1\",\n   \"Subject\": \"{\"firstRow\":{\"\":\"Job Duration Warning\"},\"effectiveIntegrationRuntime\":\"DefaultIntegrationRuntime (West Europe)\",\"billingReference\":{\"activityType\":\"PipelineActivity\",\"billableDuration\":[{\"meterType\":\"AzureIR\",\"duration\":0.016666666666666666,\"unit\":\"DIUHours\"}]},\"durationInQueue\":{\"integrationRuntimeQueue\":0}}\",\n   \"ErrorMessage\": \"Everything is okey-dokey!\",\n   \"EmailTo\": \"[email protected]\"\n}\t"
}

But then resulted in this error:

{
    "errorCode": "2108",
    "message": "{\"error\":{\"code\":\"InvalidRequestContent\",\"message\":\"The request content is not valid and could not be deserialized: 'After parsing a value an unexpected character was encountered: f. Path 'Subject', line 4, position 17.'.\"}}",
    "failureType": "UserError",
    "target": "Web1",
    "details": []
}

[Edit] The PREVIEW from the Lookup Activity is the text: Job Duration Warning BUT when I debug the pipeline, it lets me see the actual Output, which is this:

{
    "count": 1,
    "value": [
        {
            "": "Job Duration Warning"
        }
    ],
    "effectiveIntegrationRuntime": "DefaultIntegrationRuntime (West Europe)",
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "AzureIR",
                "duration": 0.016666666666666666,
                "unit": "DIUHours"
            }
        ]
    },
    "durationInQueue": {
        "integrationRuntimeQueue": 0
    }
}

So it appears that the problem is that the Lookup Output isn't what I thought it was, so the variable can't be used in the Web Activity, as it contains unsupported characters or something along those lines.

I just tested this and it worked ok:

• Create a String Parameter with the value Job Duration Warning
• Set the Variable value to be @pipeline().parameters.ParamSubject
• Include the variable in the web activity with an @ in front of it

I then receive my expected email with the right subject. I just don't know how to get the string output of my query, into a variable / parameter, so that i can use it in the web activity.

Answer 1

I don't know how well this applies to other people's issues, but I found a solution that has worked for me.

• In the SELECT query within the Lookup Activity - name the output (in my case, I called that column 'Subject'- i.e. SELECT xyz AS Subject
• In the Lookup Activity, turn on the setting 'First Row Only'
• In the Set Variable Activity, use the code: @activity('Lookup1').output.firstRow.subject (where 'Lookup1' is the name of your Lookup Activity and Subject is the name of the column you are outputting)
• In the Web Activity, reference the variable as follows:

{
   "DataFactoryName": "@{pipeline().DataFactory}",
   "PipelineName": "@{pipeline().Pipeline}",
   "Subject": "@{variables('EmailSubject')}",
   "ErrorMessage": "Everything is okey-dokey!",
   "EmailTo": "[email protected]"
}