Dynamic JSON for Web activity

[SalCag]

Hi all

I'm new to ADF.

I want to create a pipeline that executes a stored procedure from an Azure SQL database and sends an email with the result from the SP.

I followed these instructions to create a Logic App to send an email using a Web activity: How to send email - Azure Data Factory & Azure Synapse | Microsoft Learn

But now I'm struggling with sending the dynamic JSON the the Web Activity.

I have two variables: TextForMessage and Receiver.

Both are set in the Pipeline by a Set variable activities.

I added the Web activity, pointed to the Logic App, and added this for the Body, as explained in the link above:

{

    "message":"@{variables('TextForMessage')}",

    "receiver":"@{variables('Receiver')}"

}

I get an error, that the JSON is not formatted correctly:

{
"error": {
"code": "InvalidRequestContent",
"message": "The request content is not valid and could not be deserialized: 'After parsing a value an unexpected character was encountered: \". Path 'message', line 2, position 17.'."
},
"A ... <truncated>

To get the JOSN, I added another Set Variable activity and added the same expression to put together the JSON:

{

    "message":"@{variables('TextForMessage')}",

    "receiver":"@{variables('Receiver')}"

}

This is the result (I replaced the content):

{
"name": "URLBody",
"value": "json({\n \"message\":\"{\"\":\"Text to send in the email\"}\",\n \"receiver\":\"mailaddress@domain\"\n})"
}

I suppose that the expression in the Web activity generates the same output.

What can I do to get a properly formatted JSON out of it?

Any help is greatly appreciated.

Regards

Salvatore

[#2]

OK, I was able to go one step further.

I wrote a stored procedure to generate the JSON body from the output of the first SP.

Now, I can invoke the Logic App, but the body JSON arrives in a strange format:

{

  "": "{\r\n\t\"message\":\"Mail body text\",\r\n\t\"receiver\":\"email address removed for privacy reasons\"\r\n\t}"

}

I narrowed down the issue to the fact that the SP returns a set (SELECT <result>), and I have to consume this result.

I used the approach described here: How to process output from stored procedure in Azure Data Factory - Microsoft Q&A

And it looks like the result looks like that because I use this approach.

The output of the Lookup Activity already has all these "special characters.":

{

"firstRow": {

"": "{\"message\":\"Mail Body text\",\"receiver\":\"email address removed for privacy reasons\"}"

},

"effect ... <truncated>

This is the source of the issue.

I already tried to use nested replace calls to get rid of this stuff, but without success.

Any hints on this?