Sending an email notification is the traditional way of checking if the integration has been done properly or not. In ADF for sending an email, we need to configure the logic app in the Azure portal. We can send several notifications on respective events like on Success, Failure or Completion of a pipeline. We can send a result set of a query as an attachment as well. But when we need to send a result set of a query in an email as a body, we don’t get the right format.
Here I am explaining how can we show the result set with multiple records in tabular format in an email body using some simple steps.
So, for this, we have a query whose result set needs to be sent by email. Here I am using Snowflake as a database but this query can be used in SQL Server too.
Now to get it in a single record and give it an HTML table format, we need to modify it as:
I have used <tr> and <td> tags in between to give it a tabular format and got it in a single record to process it in the Lookup activity in ADF in a single go else we need to use For each activity to process each record.
Now if the logic app is already configured in the Azure portal, then create a pipeline in ADF and take a lookup activity as below and use the above query in its settings:
I have already configured a pipeline for Success Email Notification using Web Activity like the below:
Now add this pipeline to our pipeline as below:
In the pipeline PL_SendEmailSuccess settings, write the code to the parameter which is mentioned as Body in LogicApp as below:
Now we will execute the pipeline. Though the output of lookup looks messy as below:
But email we get is properly formatted in table form:
Conclusion: This type of task can be used to send the status report of the jobs which run on a daily basis, to send an alert on significant metrics, or to get the result of ETL audits with rejected records. So instead of downloading attachments, users can have a quick check using this approach.