ADF currently lacks direct support for dynamically setting the concurrency parameter of the ForEach activity based on external factors such as SQL server limits.
Instead of having multiple pipelines or a complex branching strategy, you could use a combination of Lookup and If Condition activities to determine which concurrency setting to apply:
- Lookup Activity: to read the configuration for the SQL server (which might include the maximum number of sessions allowed).
- Set Variable Activity: Use the result from the Lookup to set a variable that holds the maximum concurrency level for the current execution context.
- If Condition or Switch Activity: Use an If Condition or Switch activity to check the variable and determine the concurrency parameter to apply to the ForEach activity.
- ForEach Activities: Have separate ForEach activities configured with fixed concurrency parameters (1, 3, 5,...) and execute the one that matches your requirements based on the server's session limit.
Another approach is to use an Azure Function or an API call from ADF.
As you suggested, creating dedicated pipelines for each type of server could work but might lead to maintenance challenges (each with a fixed concurrency setting that matches the server's limits) and then use a Switch activity in the main pipeline to decide which dedicated pipeline to trigger based on the server type or configuration.
https://medium.com/@shuklaprashant9264/azure-data-factory-concurrency-and-dependencies-2f7c76f977f7