Azure Data Factory - Stored Procedure Activity

Welcome to part two of my blog series on Azure Data Factory. In the first post I discussed the get metadata activity in Azure Data Factory. Now you are going to see how to use the output parameter from the get metadata activity and load that into a table on Azure SQL Database.

Check out part one here: Azure Data Factory – Get Metadata Activity

In this blog post you are going to learn the following:

1. How to execute a stored procedure in Azure Data Factory.
2. How to use the output parameters from the Get Metadata task as input parameters for a stored procedure.

Setting up the Stored Procedure Activity in Azure Data Factory

I am going to pick up where I left off in the last blog and add the stored procedure activity to my existing pipeline.

1. Add the stored procedure activity to the pipeline, it’s found under “General”.
2. Next, connect the two activities, the stored procedure will only execute following a successful completion of the get metadata activity.

1. Click on the stored procedure activity to get to the properties window and then click on “SQL Account”.
2. Under SQL Account select the linked service to your Azure SQL Database. If you haven’t already created a linked service, click + new to create a new one.

1. Now click the “Stored Procedure” tab in the properties window.
2. Choose your stored procedure from the dropdown menu. This dropdown will display all stored procedures on your Azure SQL DB. The stored procedure in this blog is inserting the modified date of the file into a table.
3. Click on import parameter. This will import any parameters found in your stored procedure. Notice that the parameter ModifiedDate is returned in the parameter list.

Dynamically Setting the Value

The last step is to now pass a value into the parameter ModifiedDate so that the table can be properly updated. The Modified Date value produced from the Get Metadata activity is the value we want to pass into the stored procedure. You can manually type in the output parameter reference or you can use the option to add dynamic content.

• Add dynamic content by either clicking the hyperlink “add dynamic content [Alt + P]” or by clicking in the Value box and then using the hotkey [Alt + P].

1. Scroll to the very bottom of this dynamic content list and then click “Get Metadata1” under activity outputs. Unfortunately, this does not generate the full expression for returning the last modified date of the property. We still need to specify which output we want here.
2. Complete the expression by adding .lastModified.
3. Click Finish.

Tip:  Getting the exact name of the output parameter can be challenging, we discussed how to find the property name in the get metadata blog post. If you missed that blog, or forgot, now would be a good time to go back and do a quick review.

As the last step, debug and test your pipeline! Thanks for reading this blog and please check back often!