Azure: Execute Stored Procedure using Azure Data Factory

In this blog post, we’ll see how to execute Stored Procedure in Azure SQL Database inside Azure SQL Server. Let’s go through the steps to see it in action.

• Login to Azure Portal.
• Click Create a resource –> Databases –> SQL Database.

• Give a unique name to the Azure SQL Database. Click on Server field to create Azure SQL Server on the fly. 
• Give a unique name to the Azure SQL Server, provide credential for login and click Select.

• Make sure the Azure SQL Server that we just created is selected as Server while creating Azure SQL Database. Fill the mandatory fields and click Create.

• Let’s connect to the Azure SQL Server using SSMS. On clicking Connect a new pop up will open where we need to put Azure subscription credential to login.

• Using Azure subscription credential we need to create a firewall rule so that we will be able to connect to the Azure SQL Server.

• On Successful login, let’s select the option of IP address range and click OK.

• After logging in, we can see there are few records in SalesLT.Customer table.

• Let’s create a stored procedure in the same database to update “CompanyName” column of Customer table to “TestCompany” as shown below:

• Now, let’s create Azure Data Factory from Azure Portal. Click Create a resource –> Analytics –> Data Factory.

• Give a unique name to the Data Factory, fill the mandatory fields and click Create.

• Then, let’s browse through the Azure Data Factory that we created and click on Author & Monitor.

• Click Create pipeline

• Search for Stored Procedure type activity and drag it to the canvas.

• Go to SQL Account Tab –> Click New

• Select Type as Azure SQL Database, fill the Azure SQL Server details and click Finish.

• Go to Stored Procedure Tab and select the procedure “UpdateCompany” from the dropdown that we just created above.

• Once done, Publish the changes.

• Click on Trigger –> Trigger Now to trigger the pipeline.

• Click Finish.

• Go to Monitor section of Azure Data Factory and wait for the pipeline to get executed successfully.

• Once the pipeline is executed successfully, let’s verify if the Stored Procedure has been executed successfully to update CompanyName column or not of SalesLT.Customer table.

As we can see above, the column “CompanyName” has been updated as per the definition of Stored Procedure. 

So, we saw how to execute a stored procedure using Azure Data Factory.

Hope it helps !!