3 Steps to Run PowerShell in Azure Data Factory

Azure Data Factory has many capabilities. But no tool is the best at everything. Sometimes you have an existing script that needs to be automated or PowerShell is the best programming option for the task at hand. Currently, ADF does not have a PowerShell task. So, we will execute it by using a webhook to call a script stored in an Azure Automation Account.

If you do not have an Automation Account set up, go to new resources and click on IT & Management Tools, and select Automation.

Complete the few required fields and click Create.

When we go to runbooks, we may see some sample runbooks. If you did not have the authority to Create Azure Run As Account, you may not see any sample runbooks.

Create Runbook

Click on Create a run book, enter a name and type of PowerShell, and click Create.

In the Editor, paste the following code:

param (
    [Parameter (Mandatory = $false)]
    [object] $WebHookData
)
# Get all parameters from body 
# (passed from Data Factory Web Activity)
$Parameters = (ConvertFrom-Json -InputObject $WebhookData.RequestBody)

# Save Parameters for readability
# Check for ADF parameter. Not used when testing interactively
If ($Parameters.callBackUri)
{
    $callBackUri = $Parameters.callBackUri
}
# Demo parameter
$ParmMsg =  $Parameters.ParmMsg

# Main Script
Write-Output $ParmMsg
 
 
# If called from a webhook, use callBackUri to notify task is complete
If ($callBackUri)
{
    Invoke-WebRequest -Uri $callBackUri -Method Post
}

Let’s review the sections.

1. Parameters: Accept parameters if passed, read the JSON body, set parameter variables for readability.
2. The main body of the script
3. Post the callback URI to let Data Factory know it has been completed. Otherwise, ADF will wait until the time-out limit is reached.

Save then Publish.

Create Webhook

From the Overview pane, click Add webhook.

Fill out both sections. First Create a new webhook.

Enter a name for the webhook. The default is to expire the webhook in one year. You can override this if you want to. Copy the URL. You will not have access again because it contains a security token. Click OK.

You must go into the Parameters and run settings even if we won’t set any for our template.

Leave the defaults and click OK.

Now the Create button is highlighted. Click Create.

Verify it was created in the webhooks pane.

Data Factory Webhook

In your pipeline, create a WebHook activity.

Enter the webhook URL you saved. The method will be POST. Headers will be Content-Type. And the body will be our demo parameter.

If you remember the PowerShell script, you may be asking where the callBackUri is. ADF adds this to the body for you. See WebHook documentation.

Click Debug to run the webhook activity. After it completes, we can go check the output in the Runbook.

Click on the jobs pane and select the most recent (top) job.

We can see the CallBackURI added to the webhookData

Under Output, we can see our message.

Conclusion

Three steps to add another tool to your toolbelt.

1. Create a runbook from the template.
2. Create webhook.
3. Execute from ADF WebHook activity

This will give you the capability to automate more tasks in Azure and use PowerShell when it is the best language for the processing you need.