(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! :-)
How did you pass in email body, email subject and email recipient variables into request body ?
ReplyDeleteThat'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')"}
DeleteGetting 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.'.\"}}",
Delete"failureType": "UserError",
"target": "EDWCheckSum",
"details": []
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.
DeleteI have the same error as Unknown. Can you show what is happening at the 'Set Email Body' variable?
DeleteI 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
Deletetanx
ReplyDeleteHi Rayis, I thinks this is a great solution, but I would like to ask:
ReplyDeleteHow 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?
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
DeleteLet me know if this helps.
I don't want to send email if query not returning any data how to do that
ReplyDeleteYou can do this by checking the output of the Lookup task, if it's empty then do nothing.
DeleteHello, 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
ReplyDeleteIt's all up to you to work with the required HTML tags and then put them into your SQL query.
DeleteThank you! Works brilliantly. Now I'm on my own trip to further format.
ReplyDeleteFor 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.
Thank you for your feedback!
DeleteYes needs a step showing passing output to Web Logic App call as I too get an error
ReplyDeleteThis 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
DeleteMy requirement is exact the same but am not able to achieve it. Can we have the end to end steps listed for this activity?
ReplyDelete1) 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.
DeleteHi, thank you! Is it possible to send an email notification for each row of the output?
ReplyDeleteYes, this would be possible. You would just need to control the output and potentially loop each result line via your web email activity.
DeleteThanks rayis for the solution, my requierment is i need to run 4 sql and all 4 results i need to sent into gmail , i am following as as you mentioned but added 4 lookup activity and add 4 set variable to capture the email results but when passing variable into web activity for 1 variable its working but if i add 2 variable it will run the pipeline but results are not passing
ReplyDeleteIf your use case is to combine the output of 4 variables in a single email message, then you could do this with an additional variable that you either append or combine all 4 variables at once. Then you can validate the value of variable and pass it to your email output task. The other thing I would check is how you assign your 2nd and other variables for your email task, perhaps the variable 1 overrides the values of other values, but I'm not sure since I can't see your ADF workflow.
Delete