Azure Synapse Pipeline - ForEach in ForEach not working

Question

In Synapse I have a pipeline for orchestrating my work.

The first I do is to lookup the distinct priorities I have on my jobs. Then I create a ForEach for each of my priorities.

In that ForEach loop, I create another lookup which looks for my tasks with that specific priority and with another ForEach loop I will execute each task.

If I do this directly in the same pipeline, I get an error:

Error
{
    "code": "BadRequest",
    "message": null,
    "target": "pipeline//runid/ab56b3c8-244f-4e8a-b5cd-2cfff775e16c",
    "details": null,
    "error": null
}

But if I copy my inner Lookup task and ForEach loop to another pipeline and add the priority as a parameter, it works like a charm!

So: Is it possible to have a ForEach loop within a ForEach loop in Synapse or not?

Answer 1

Nested logic operations in pipelines (both ADF and Synapse) are very limited. For example, you cannot place a ForEach or Until inside a ForEach, nor can you place any logic operation inside an If or Switch.

The way to work around this is by calling another pipeline (Execute Pipeline) and passing any relevant parameters. That pipeline would then be designed to perform operations over a single iteration. This creates a parent-child pipeline relationship. Depending on your scenario, you may need to run these sequentially by specifying "Wait on completion".