Loop through Multiple Stored Procedures with Parameters & Generate Files Dynamically in Azure Data Factory

Issue: How To Loop through Multiple Stored Procedures with Parameters & Generate Files Dynamically in Data Factory.

In this article, we are going to learn, to loop through multiple stored procedures with parameters & Generate files dynamically in the Azure data factory, let's start our demonstration.

Open the Azure Data factory studio, go to the Author tab, click on the pipeline, then click on New pipeline.

Find and drag the Lookup activity, go to the settings tab, click on the + New button to create a new source dataset.

Select Azure SQL database, then click on Continue.

Name your dataset, then create a linked service, provide a name, select your Azure subscription, select the server name, select the database name, choose the authentication type, provide the user name and password, then click on Create.

Once our linked service is created, select none for import schema and then click on Ok.

In the settings tab insert the query which is:

(Select 'EXEC '+spname+ ' '''+SPpValue+'''' AS SP,FileName+'_'+Cast(Cast(getdate() as date) as varchar(100)) as FileName from dbo.SPList)

then uncheck the First row only.

Next, find and drag the ForEach loop Activity, then connect with the lookup activity, go to the settings tab, and add the dynamic content which is our lookup activity, then at the end add ''.value''. 

Go inside the ForEach loop activity, Find and drag the copy data activity, go to the source tab, click on the + New button to create a new source dataset.

Select Azure SQL database, then click on continue.

Name the dataset, select the linked service that we have created earlier, select none for import schema, then click on ok.

In the source tab add the dynamic content which is our ForEach loop, at the end add ''.SP''.

Next, go to the sink tab, click on the + New button to create a new sink dataset.

Select Azure blob storage, then click on continue.

Select file format as CSV, then click on continue.

Name your dataset, select the linked service, select the first row as header, select none for import schema, then click on Ok.

In the sink, tab click on Open, go to the Parameters tab, and create a parameter, then go to the connection tab and use that parameter there.

In the sink tab, map the parameter with our ForEach loop, and add ''.Filename'' at the end, then go ahead and Debug the pipeline.

Video Demo: Loop through Multiple Stored Procedures with Parameters & Generate Files Dynamically in Azure Data Factory