topic Re: How to call stored procedures Concurrently and sequentially through Data pipeline in Pipelines

How to call stored procedures Concurrently and sequentially through Data pipeline

jlakshminaraya — Wed, 07 Feb 2024 16:12:04 GMT

Hello -

I have 10 stored procedures in Fabric Warehouse.

First 5 stored procedures to be run concurrently and remianing 5 to be run in sequentially by using Datapipeline. Please help us how to accomplish this in Microsoft Fabric?

Thanks in advance.

Thanks,

Lakshmi Narayana

Re: How to call stored procedures Concurrently and sequentially through Data pipeline

NandanHegde — Wed, 07 Feb 2024 16:36:38 GMT

You can have 2 variables :

1 variable with the parallel SP names semi colon seperated and another variable with the sequentail SP names semi colon seperated.

You can then use 2 for each activities (1 with sequentail flow and 1 with parallel flow) with input as split(variable,';') which would create an array for both the variables and within for each use SP activity

Re: How to call stored procedures Concurrently and sequentially through Data pipeline

Anonymous — Wed, 07 Feb 2024 17:16:38 GMT

Hi jlakshminaraya
Thanks for using Fabric Community.
You can achieve the above requirement by using a layout as shown below:

Hope this helps. Please let me know if you have any further queries.

Re: How to call stored procedures Concurrently and sequentially through Data pipeline

jlakshminaraya — Wed, 07 Feb 2024 17:24:00 GMT

Anonymous

Thanks for the update. Hope this work.

Re: How to call stored procedures Concurrently and sequentially through Data pipeline

Anonymous — Wed, 07 Feb 2024 17:25:26 GMT

Hi jlakshminaraya
Please do let me know after you try this. Incase of any queries please feel free to reach out to us.
Glad to help.

Re: How to call stored procedures Concurrently and sequentially through Data pipeline

jlakshminaraya — Wed, 07 Feb 2024 17:27:36 GMT

NandanHegde : Thanks for the update. Since i am new to Fabric , could you please share the steps in pictorial approach with sample Variable and SP names and corresponding setting tabs.

Thanks,

Lakshmi Narayana

Re: How to call stored procedures Concurrently and sequentially through Data pipeline

Anonymous — Wed, 07 Feb 2024 17:33:35 GMT

Hi jlakshminaraya
Are you asking the same query here : How to call multiple procedures in sequence or con... - Microsoft Fabric Community?

Re: How to call stored procedures Concurrently and sequentially through Data pipeline

Anonymous — Wed, 07 Feb 2024 17:41:45 GMT

Hi jlakshminaraya
The approach which I mentioned will definitely work. I have done a repro from my side.

If you observe the pipeline runs, all the stored procedures from 1 to 5 are run at the same time. (Concurrently) and the rest of them 6 to 10 are run sequentially.

Hope this helps. Please let me know incase of further queries.

Re: How to call stored procedures Concurrently and sequentially through Data pipeline

NandanHegde — Thu, 08 Feb 2024 04:14:32 GMT

Hey jlakshminaraya

The details provided by Anonymous is correct but it is too manual and in case of new additions, you would have to manually add in new activities.

The below design is a meta data driven flow :

1) Create 2 variable like ParallelExecution and Sequentailexecution with semicolon seperated values

wherein values would your SPname1;spname2 etc

2) use 2 foreach activities one for parallel execution and another one for sequential execution with values for thier settings :

value :

@split(variables('ParallelExecutionSPs'),';')

Similarly for sequentail :

@split(variables('SequentailExecutionSPs'),';')

3) within for each , use stored procedure activity

similar would be for sequentail foreach as well

hope this helps.

You can even have the variable as type array and directly use in foreach as is if need be