question

NikunjPatel-2604 avatar image
0 Votes"
NikunjPatel-2604 asked ·

How to make sequential property paramterize in foreach activity ?

In pipeline we have to set sequential property of Foreach activity from pipeline parameter. Can anyone have any idea How we can pass this dynamic parameter in sequential property of Foreach activity

Use case : main purpose of pipeline to execute store procedures based on meta data configured in tables.
We have following two tables.
JobTable (JobId,JobName,IsSequence)
StepTable(JobId,ProcedureName) (one job having multiple ProcedureName in this table. One to many relationship)

In pipeline we use following logic.
[1] First use Lookup to get all Storeprocedures name bases on JobName parameter.
[2] Next step to use for each activity to iterate the above list.
[3] Inside forEach activity use Storeprocedure component to execute the store procedure based on list.

Now plan to pass one more parameter like Sequence with bool type. Based on Sequence parameter value foreach activity work like in sequential or parallel to execute store procedures using storeprocedure component.

azure-data-factory
· 3
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @NikunjPatel-2604 and thank you for your question.

I do not think this is possible, but can you please share what the use-case of making the sequential property dynamic?

Usually the decision of whether to be sequential or not, depends upon what child activities are inside the foreach activity.

If you tell me more about your situation I may be able to help find a work-around.

0 Votes 0 ·

Added use case in above question. Let me know anything more you need from same.

0 Votes 0 ·

What type of parameter are you using? (String, array, etc.)
And what format? (comma separated, json, etc.)

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered ·

Thank you for sharing the use case.

The original ask was to decide during runtime whether the forEach runs sequentially or in parallel.

Ideally this would be done by placing the forEach inside an IfCondition activity or Switch activity.
However, at the present time, placing the forEach inside IfCondition activity or inside Switch activity is prohibited.

I do have a way to work-around this limitiation. I will replace the IfCondition activity with a conditional of my own making. See pictures below.

10083-dynamic-sequential.jpg

Expression in second setVariable (boolean): @if(equals(variables('isSequence'),true),true,'IamStringThatBreaksTypeError')

The key to this, is I intentionally cause the second setVariable activity to fail when isSequence is false. This way, when isSequence is true, we follow the success dependency to do the ForEach with the sequential flag. When isSequence is false, we follow the failure dependency to do the ForEach without the sequential flag. This may not be exactly the same as dynamically setting the flag, but it gets you the same result.

The skipped dependency keeps this intentional failure from causing the pipeline to report failure status.


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Majaffer for your workaround solution. But in this solution there are 2 cons.
[1] For each activity code need to duplicated. [2] Its not fully dynamic. It means if need to pass batch count as parameter then it wont possible.
So far based on my requirement it works fine, and I can partially accept answer.

0 Votes 0 ·
JohnAherne-0132 avatar image
0 Votes"
JohnAherne-0132 answered ·

Not an easy problem to solve, but you could do it like this:


Pipeline 1 1. Lookup - Pulls a sorted list of stored procedures in json format. The data will need to be in json format for a ForEach activity (e.g. instead of JobID, StoredProc you would want jobID,{'SP':'StoredProc'}. For anything you want to run in parallel, it would be jobID,{'SP':'StoredProc1','SP':'StoredProc2'} 2. For each item in the lookup (Sequential turned on): 3. Call Pipeline 2, pass @itemas parameter


Pipeline 2 1. ForEach item in parameter (This would be parallelized to the maximum number of jobs you want to run in parallel) 2. Run stored proc


Pipeline 1 would pass in a line of stored procedures in order. If there is only 1 stored procedure in the list, that would be executed on its own. If there is more than one stored procedure in the list, they would be executed in parallel.


Again, not easy, but it should work.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.