In my last post titled Fabric: Load Data from a CSV File Stored in Azure Blob Storage to Azure SQL DB, I documented one way to create a pipeline to load data from a CSV file stored in Azure Blob Storage to an Azure SQL Database in a new Fabric workspace.

In this post, I explore the Office 365 Outlook activity available (in preview at the time of this writing) for Fabric Data Factory pipelines by adding it to my LoadAndyWeatherData pipeline created in the post titled Fabric: Load Data from a CSV File Stored in Azure Blob Storage to Azure SQL DB.

Add an Office 365 Outlook Activity

I begin by opening the pipeline LoadAndyWeatherData, the pipeline I built in an earlier post. I rename the Copy data activity to “Copy_AW_CSV” to better represent that I am copying data from a CSV file stored in Azure Blob Storage, containing data collected from my AndyWeather weather station:

Next, I add a Script activity, connect an “On success” output from the new Script activity to the “Copy_AW_CSV” activity, set the Timeout property to 10 seconds, and then rename it “Truncate Target”:

The next steps are:

1. Click the Settings tab
2. Set the “Data store type” property to External
3. Set the Connection property to the target Azure SQL Database connection
4. Set the Script option to Query
5. Enter the script – “Truncate Table dbo.WxReading” in this case:

Next:

1. Click the Activities tab
2. Click the “Office 365 Outlook” activity to add the activity to the pipeline surface
3. Set the Name property (my activity is named “Email Andy”)
4. Add an optional description
5. Connect an “On completion” output from the “Copy_AW_CSV” activity to the new “Email Andy” activity:

The next steps are:

1. Click the Settings tab
2. Click the “Sign in” button to grant Fabric access to Office 365 Outlook:

When the “Sign in to your account” dialog displays, pick an account with access to Office 365 Outlook:

When the “Confirmation required” page displays, review the settings and, if you concur, click the “Allow access” button:

When you click the “Allow access” button, focus returns to the pipeline design surface. The next step is:

1. Configure the “To” property, setting it to the target email address (I will be emailing myself):

Next:

1. Click inside the Subject property value textbox
2. Click the “Add dynamic content [Alt + Shift + D]” link that appears just beneath the Subject property value textbox:

When the “Pipeline expression builder” blade displays, I enter the expression “@concat(pipeline().PipelineName, ‘ pipeline status: ‘, activity(‘Copy_AW_CSV’).status)”:

The expression I enter is:

@concat(pipeline().PipelineName, ' pipeline status: ', activity('Copy_AW_CSV').status)

The expression means:

@concat(pipeline().PipelineName, ' pipeline status: ', activity('Copy_AW_CSV').status)

The @ means “what follows is an expression.” “concat” is an abbreviation for “concatenate,” which is a function to join strings together.

@concat(pipeline().PipelineName, ' pipeline status: ', activity('Copy_AW_CSV').status)

“pipeline().PipelineName” is a System Variable expression that returns the name of the pipeline. The pipeline name is the first string to join.

@concat(pipeline().PipelineName, ' pipeline status: ', activity('Copy_AW_CSV').status)

“‘ pipeline status: ‘” is a literal string that we want to append as the second string.

@concat(pipeline().PipelineName, ' pipeline status: ', activity('Copy_AW_CSV').status)

“activity(‘Copy_AW_CSV’).status” is the status of the Copy_AW_CSV activity that we want to append as the third and final string.

I choose to read the status of the preceding activity (“Copy_AW_CSV”) because I chose to connect “Copy_AW_CSV” to the “Email Andy” activity using the “Copy_AW_CSV” activity’s “On completion” output.

If the “Copy_AW_CSV” activity execution succeeds, the expression builds an email subject that reflects the pipeline execution succeeded:

If the “Copy_AW_CSV” activity execution fails, the expression builds an email subject that reflects the pipeline execution failed:

Best of all, one Office 365 Outlook activity covers succeeded and failed notifications.

Note: The green circle containing a white checkmark indicates the pipeline execution succeeds regardless of the “Copy_AW_CSV” activity status. My expression is doing what I built it to do, but the result appears confusing – to me, at least.

Let’s fix that (well, as much as we can).

If I edit the Subject expression to read:

@concat(pipeline().PipelineName, ' | Copy_AW_CSV activity status: ',activity('Copy_AW_CSV').status)

, the “success” email subject now appears thus:

I like this better because the green circle with the white checkmark is near the pipeline name and I can train my brain to interpret this to mean that the pipeline succeeded (which is accurate). Also accurate is the fact that the pipeline may succeed even when an activity executing as part of the pipeline fails. I conducted some limited testing and my best interpretation of the results (i.e. “my best guess”) is the pipeline execution status may be linked to the execution status of the last activity to execute – but I could be wrong about that. If that’s the case, I would prefer a “Fail Pipeline on Activity Fail” pipeline option or – better yet – a Maximum Error Count property like the property found in SSIS.

To build the email body, click the “message-bolt” icon (I totally made up that name…) to the right of the Body property editor:

An initial view of the content widget (I continue to make up names) appears as shown here:

If you click “See more”, the view expands:

Add some text to the email message body editor, for example:

Place the cursor where you would like to add a dynamic content placeholder in the email message body editor.

Click dynamic content placeholders to add them to the email message body editor, for example:

The email generated by this configuration appears similar to that shown here:

While not exhaustive, I believe this walk-through provides a sense of the power of notifications via email surfaced by the Office 365 Outlook activity. I welcome your thoughts in the comments!