Issue: How to Use Stored Procedure with Parameter in Copy Activity in Azure Data Factory.
In this article, we are going to learn how to use stored procedures with parameters in copy activity in Azure Data Factory, lets start our demonstration.
Open the Azure data factory, then go to the Author tab and click on + Sign to create a new pipeline, then find and drag the lookup activity.
Go to the settings tab and then click on the + New button to create a new Source dataset.
Then select Azure SQL Database and then click on continue.
Name the Dataset, then select the linked service if you have already created, otherwise create a new linked service, and then select none for import schema and then click ok.
Then write the query from where we will read the files.
Go to the pipeline again and find and bring another Foreach activity and connect with the Lookup Activity.
Click on the foreach activity and then go to the settings tab and then add the lookup expressions in the description bar.
Click on the lookup then write the .value and then click on ok.
Then click on the pencil sign on the foreach loop activity and then go inside and add Copy activity inside.
Find and bring the copy data activity inside the foreach loop activity.
Click on the copy data activity, then go to the source tab, and then click on the + New button to create a new source dataset.
Select the Azure SQL database and then click on continue.
Name the source dataset, then select the linked service, select none for import schema and then click on ok.
Click on the stored procedure and then in the dropdown select you stored procedure.
Click on the Import parameter and in the value bar add the dynamic content which is coming from our foreach loop activity.
Click on ForEach iterator then add .RegionName and click on ok.
Then go to the sink tab and click on the + New button to create a new sink dataset where we will write the data,
Select Azure SQL database and click on continue.
Name the dataset, then select the linked service, select none for import schema and then click on ok.
Click on the Open button and then select the file name in which we have to write the data.
Click on the debug and it will read the data and write to our selected location.
Video Demo: How to Use Stored Procedure with Parameter in Copy Activity in Azure Data Factory