Use Lookup and For-Each activity and Iterate the SQL Procedure Activity in Pipeline in Azure Data Factory

Requirement: I have a SQL procedure which has the input parameters and I have SQL view which has few numbers of rows. How can I run the stored procedure for each value in that SQL view from the pipeline in Azure Data Factory

Solution:

1. Create procedure in a SQL database with input parameter



2. SQL view present in SQL server



3. Log into azure portal and click on existed or new data factory. Next click on Author & Monitor
4. New window will open, click on Create Pipeline. Enter the name of the pipeline



5. On the left side, we had list of resources like pipelines, datasets and dataflows. Click on dataset and click on right ellipses and click on new dataset
6. New tab will be opened. Search for the SQL server, select that and click on Continue
7. Enter the name and Select Linked service which is having connection to source SQL server
8. Select the table and click Ok and then publish this dataset.



9. Click on Connection and select the Preview data. Here we can see that it fetched all the records from the view



10. Now go to the pipeline. Search Lookup activity and drag it into the editor

Make data easy with Helical Insight.
Helical Insight is world’s best open source business intelligence tool.

Lets Start Free Trail

11. Enter the name, click on Settings tab and select the dataset created in above steps. Click on preview data to see the data



12. On the Activities tab, search ForEach activity and drag it into the editor. Add the connection from Lookup to ForEach
13. Enter the name, click on Settings tab. Enable Sequential property as true which means process will one for one value at a time. In Items enter value as “@activity(‘Lookup’).output.value”. It means that output value from Lookup activity will be passed to ForEach activity



14. Now click on Activities(0) tab and add activity. Drag stored procedure activity to editor



15. Click on Stored Procedure. Enter name and click on SQL Account
16. Here we have to select the SQL datasource which we created using Gateway to connect on-premises SQL server. Click on test connection



17. Click on Stored Procedure tab



18. Click on the select dropdown of Stored procedure name and select the procedure which we created in first step

Make data easy with Helical Insight.
Helical Insight is world’s best open source business intelligence tool.

Get your 30 Days Trail Version

19. Next click on Import Parameter. It will import the parameter of that SQL procedure



20. In the above image we can see that parameter got imported. Click on Value input and here there is an option to enter dynamic content. Click on Add Dynamic Content. It will open a new window



21. Here we have pre-defined system variables, functions and parameters. Enter the output from ForEach acitivity. @item() will return a single item at a time from the ForEach activity and ProcedureName is the column name from the SQL view



22. Click on Finish and parameter will be added as a value to the Store procedure



23. Save the pipeline and Execute the pipeline
24. Lookup and ForEach activity will be executed once and Stored Procedure activity will be executed three times as we have three rows in SQL view

In case if you have any queries please get us at support@helicaltech.com

Thank You
Ramu Vudugula
BI Developer
Helical IT Solutions Pvt Ltd

Best Open Source Business Intelligence Software Helical Insight Here

A Business Intelligence Framework

Free
Download

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download