Sending email notification from Azure Data Factory

I have already written about sending e-mails from ADF, you can find the contribution here, now we just add something to it.

Hard coded setting of email notification is not flexible. Every change needs deploy of ADF pipeline. Better is to store email notification metadata somewhere and dynamically load needed information to ADF. This is the simpliest setting.

1. Define form of metadata
2. Metadata storage
3. Identification of the notification
4. Load of metadata to ADF
5. Call of ADF pipeline to send email

Metadata

At first we need some place to store our metadata. What about azure sql database? 🙂 You can synchronize table with for example SharePoint Online List.

CREATE TABLE [meta].[dEmailNotification](
	[EmailNotificationId] [int] NOT NULL,
	[AdfPipelineName] [nvarchar](200) NULL,
	[EmailSubject] [nvarchar](200) NULL,
	[EmailBody] [nvarchar](4000) NULL,
	[EmailRecipients] [nvarchar](200) NULL
) ON [PRIMARY]
GO

When you need metadata for email you can just run ADF activity “Look up” with proper select.

EmailNotificationId – just unique number
AdfPipelineName – identification of ADF pipeline from which will be notification sent (exactly the same name).
EmailSubject – subject of notification
EmailBody – message
EmailRecipients – email addresses separated by commas

EmailSubject and EmailBody are specific in that, that we actually want to generate their content dynamically. It is sometimes necessary to insert in them information from executed ADF pipeline, for example run state, some look up value or pipeline triggered time. That I will show you below inside ADF section.

Now we need special ADF pipeline which will serve for email notification sending. We will call this pipeline from business pipelines.

ADF input parameters
pPipelineName – name of business pipeline
pPipelineState – status of business pipeline Fail/Success.
pErrorMessage – if status is Fail we need some error message.
pLoadDetails – any additional information about business pipeline.
pSendOnlyOnP – will we send notification only on production environment?

In Look Up we will be testing if there is any notification definition for pipeline name passed by parameter pPipelineName.

SELECT
ISNULL
    (
        (
            select top 1 EmailNotificationId from [meta].[dEmailNotification] where AdfPipelineName = '@{pipeline().parameters.pPipelineName}'
        )
    ,0
    ) AS EmailNotificationId

If there is some notification, result will be Id, if not then 0. Result will be tested in If activity.

In True section of If we will prepare notification details. Look Up will get metadata from database table (subject, body, recipients).

select top 1 * from [meta].[dEmailNotification] where AdfPipelineName = '@{pipeline().parameters.pPipelineName}'

In Set variable activities is treatment of any empty input parameters. If we pass empty parameter to the next pipeline that sends emails it will fail. We just coalesce input parametr with empty value.

Special activity is Set_StateColor. There color is set according to the final status of the pipeline, so that the email can be colored.

Next activity is execution of our send email pipeline from previous blog post.

pMessage & pSubject

And now the magic.
If you want to fill notification with specific informations from business pipeline you need connect static email template with dynamic execution of ADF pipeline.
This link is created on the basis of parameters that are used in email templates and in ADF it is replaced by the dynamic value from pipeline processing.

Example of email template

<div style='border: black solid 1px;padding:5px;background:##stateColor##;'>
<h3>Load Info</h3>
<strong>Environment:</strong> ##environment## <br />
<strong>Date:</strong> ##date##<br />
<br />
##loadDetails##
<br />
<br />
##error##
</div>

Replace in call of ADF pipeline for email sending

@replace(
replace(
replace(
replace(
replace(
replace(
activity('Get_notification_settings').output.FirstRow.EmailBody,
'##status##',
pipeline().parameters.pPipelineState
),
'##environment##',
variables('vEnvironment')),
'##error##',
variables('vErrornMessage')),
'##date##',
pipeline().parameters.pDate),
'##stateColor##',
variables('vStatusColor')),
'##loadDetails##',
variables('vLoadDetails'))

These parameters are ready for email templates. It is possible to have more or less, you just need to specify them all in replace in passing parameters to send email pipeline.
##status##
##environment##
##error##
##businessdate##
##stateColor##
##loadDetails##