Forum Discussion

SalCag's avatar
SalCag
Copper Contributor
Jun 29, 2024

Dynamic JSON for Web activity

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
  • SalCag's avatar
    SalCag
    Copper Contributor
    Jun 30, 2024

    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.
     
    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?

Resources