In this blog article, we will explore the process of calling a stored procedure with parameters using Azure Data Factory.
We will first create a dummy stored procedure in Azure SQL DB called DemoSP as shown below:
Next, we will navigate to Azure Data Factory and proceed to create a pipeline, following the step-by-step instructions provided below:
Subsequently, we will rename the pipeline:
From the Activities panel within Azure Data Factory, we will drag and drop the Stored Procedure activity onto the pipeline canvas.
Let’s proceed by renaming the pipeline to “DemoSP”.
Click on the “New” button to create a Linked Service, and once the Linked Service is successfully created, select the “DemoSP” stored procedure from the dropdown list.
To establish the connection with Azure SQL Database, fill in the required details for the Linked Service as illustrated below. After adding all the necessary information, click on “Test Connection” to verify the connection and then proceed to click on “Create” to successfully create the linked service.
After clicking on the “Create” button, you will be redirected to the Settings of the Execute Procedure activity. Within this section, you can easily select the “DemoSP” from the dropdown menu. By clicking on the “Import” option, the stored procedure parameters will be automatically imported. To provide values for these parameters, simply fill in the corresponding “Value” fields.
After finalizing the modifications, you can publish the changes to make them active. Once published, you can trigger the pipeline to initiate the execution of the stored procedure. Click on “Publish all” to publish the changes.
Click on “Add trigger” -> “Trigger now” in order to execute the pipeline once. If you want to schedule the execution, click on “New/Edit”. This way you can create a trigger and schedule it to run as you need.
So there you have it! The stored procedure has been successfully executed.