Send a Database Query Result as Email via Logic App in HTML format using Azure Data Factory

(2020-June-07) It is a very simple concept, you have a database query result, either a direct database table output or a stored procedure returned result that needs to be sent as email notification. This email could serve as a status report of your daily data processing job or alert notification with some metrics that require immediate attention, and you want to be as user-friendly as possible with this message aka HTML format for your dataset is required.


I've built this solution approach over a year go and wanted to document and share it after recently using it in one of my projects. Again, I agree, there other more or less effective ways to achieve the very same goal, this blog post is just a recollection of my efforts to send HTML formatted SQL query results using Azure Data Factory and Logic App.

(A) Database Query
Let's say I want to send an email message with the Top 10 Selling Products using Microsoft AdventureWorks Sales LT database:

I also created a stored procedure to prepare an HTML output result. This where the whole trick is created (or the purpose for this blog post = spoiler alert :-). Knowing, that my Azure Logic App to transmit email message usually takes one variable for email body message then this drives my efforts to convert SQL based multiple records' result into a single string data structure along with HTML formatting options applied.

1) First I need to use <li> HTML tag to list all of my dataset records
2) Then I need to wrap this list of items with the <ol> HTML tag to add numbers for each of the lines (your case might be different: a table definition or text coloring might be necessary).

Achieving my first requirement was easy by combining all the data elements into one column with the "li" name, and the second requirement was accomplished by converting all my records into one data cell with FOR XML RAW ('') option and adding "ol" as my ROOT.

The output of this query is not very pretty but you need to see how my ugly duckling converts into a beautiful HTML swan :-)


The rest is easy.  

(B) Azure Logic App
My Logic App has an HTTP trigger and one "Send an email" action


(C) Azure Data Factory
The main job of my Azure Data Factory (ADF) pipeline is done by the Lookup activity, the only ADF activity that could return output result of a SQL Server stored procedure, regular Stored Procedure Activity in ADF won't help me with this.



The output of my Lookup activity looks ugly as expected, but we only care that it should come out as one single string data element:


(D) Received HTML formated email message
After passing the output of my stored procedure via ADF Lookup activity to my Logic App, I received an email message as I expected: all lines formatted and nothing more.


Little did I know how much can be done with this FOR XML T-SQL clause and embedding HTML tags within. 
My data adventure continues! :-) 

Comments

  1. How did you pass in email body, email subject and email recipient variables into request body ?

    ReplyDelete
    Replies
    1. That's easy. Create 3 variables and them pass them in the Body of my Web Activity request as this: {"EmailTo":"@variables('var_email_to')","EmailSubject":"@variables('var_email_subject')","EmailBody":"@variables('var_email_body')"}

      Delete
    2. Getting an Error with the when hitting the Logic App HTTP. "message": "{\"error\":{\"code\":\"InvalidRequestContent\",\"message\":\"The request content is not valid and could not be deserialized: 'Unexpected character encountered while parsing value: v. Path 'EmailBody', line 2, position 17.'.\"}}",
      "failureType": "UserError",
      "target": "EDWCheckSum",
      "details": []

      Delete
    3. Hello, Unknown, it's hard to tell what you're passing to your Logic App call. Test if manually first, when you succeed, make sure to create and a similarly structured message to your web call.

      Delete
    4. I have the same error as Unknown. Can you show what is happening at the 'Set Email Body' variable?

      Delete
    5. I don't think I have kept the pipeline from this blog post. However you can search for the other pipelines in my Github repository. https://github.com/NrgFly/Azure-DataFactory/tree/master/Samples/pipeline

      Delete
  2. Hi Rayis, I thinks this is a great solution, but I would like to ask:

    How do you configure the "set variable" with "set email body" so that it captures the output of the "lookup"? I don't think that's explained in the post and I'm green as a dollar, would you mind helping me out?

    ReplyDelete
    Replies
    1. Hi Guillermo, I missed that part. Run your Lookup in a Debug mode, check the output of that Lookup activity and make the plans to reference the Output of this lookup further down your pipeline. Also, check other posts that share more information about working with variables: http://datanrg.blogspot.com/search?q=variable

      Let me know if this helps.

      Delete
  3. I don't want to send email if query not returning any data how to do that

    ReplyDelete
    Replies
    1. You can do this by checking the output of the Lookup task, if it's empty then do nothing.

      Delete
  4. Hello, instead of using pipeline(|) as a separator, I want to enter the field in separate line. However, it should still be the part of List item

    ReplyDelete
    Replies
    1. It's all up to you to work with the required HTML tags and then put them into your SQL query.

      Delete
  5. Thank you! Works brilliantly. Now I'm on my own trip to further format.

    For those who have not done so, it could use a step showing how to capture the output of the lookup in the variable. It could also use another to show how to call the Variable in the Logic App call.

    ReplyDelete
  6. Yes needs a step showing passing output to Web Logic App call as I too get an error

    ReplyDelete
    Replies
    1. This post didn't have many details about creating a payload to Logic App from ADF using Web Activity, however I've blogged more about it earlier. Check this post and see if it can provide more information that you might be looking for: https://datanrg.blogspot.com/2018/11/email-notifications-in-azure-data.html

      Delete
  7. My requirement is exact the same but am not able to achieve it. Can we have the end to end steps listed for this activity?

    ReplyDelete
    Replies
    1. 1) Build your SQL query the way you want and let the ADF Lookup activity return the results; (2) Pass the output of the Lookup activity to your web call as Body element for an Logic App; (3) Logic App consumes the payload from the ADF web activity call and sends the email. All these end to end steps are described in the blog post. I didn't add anything new.

      Delete
  8. Hi, thank you! Is it possible to send an email notification for each row of the output?

    ReplyDelete

Post a Comment