Running a Stored Procedure for Multiple Dataflow Activities or notebooks

Question

How do I run a single stored procedure multiple times in an Azure Pipeline for multiple dataflow activities? I'm having trouble figuring out the best way to do this.

Answer 1

Hi Aditya Singh ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

As per my understanding you are trying to call multiple stored procedure within dataflow via ADF pipeline. Could you please explain the need to run the stored procedure via dataflow or notebook, when ADF has "Stored procedure" activity specifically to execute the Stored procs.

You could consider leveraging stored procedure activity in ADF pipeline to execute stored procedure. In order to run multiple stored procedures, you can follow the below steps:

• Create a control table in the Az SQL DB which contains the list of all stored procedures to be called along with the storing the list of input parameters. Point to the control table via lookup activity.
• Use Foreach activity to loop through the lookup output, taking each SP name for each iterations.
• Inside foreach, call stored procedure activity and use 'add dynamic content' to pass the SP name with expression , eg: @item().SPName

In case you want to call the SP within dataflow, then instead of stored procedure activity, use dataflow activity and pass the SP names dynamically for each iteration as a parameter to the dataflow .

Hope it helps. Kindly accept the answer by clicking on Accept answer button. If you have any further query, kindly let us know. Thankyou.