First, a couple shoutouts to my brothers and friends, Joel Cochran (@joelcochran) and Shannon Lowder (@shannonlowder). This approach grew out of conversations with them.

The ForEach activity iterates over a collection of items. One less-documented property of the ForEach activity is it (currently) iterates the entire collection. I can hear some of you asking…

“What if something fails inside the ForEach activity’s inner activities, Andy?”

That is an excellent question! I’m glad you asked. The answer is: The ForEach activity continues iterating.

I can now hear some of you asking…

“What if I want the ForEach activity to fail when an inner activity fails, Andy?”

Another excellent question, and you’ve found a post discussing one way to “break out” of a ForEach activity’s iteration.

One caveat regarding the solution that follows: this solution halts the execution of the pipeline. If you wish to have the pipeline

First, a List

My list is a collection of name-value pairs retrieved from an Azure SQL database table named dbo.List:

Next, I create a new ADF pipeline and add a Lookup Activity to the pipeline canvas. I named my Lookup activity “Lookup List” and configured the settings to return the name-value pairs in the dbo.List table:

After this, I add a ForEach activity to the pipeline and connect a Success output from the “Lookup List” lookup activity to the new ForEach activity. I rename the ForEach activity “ForEach Value” and configure the following settings on the Settings tab:

• Sequential: Checked
• Items:
  • Click the “Add dynamic content [Alt + P]” link
  • When the Add dynamic content blade displays, select Activity outputs > Lookup List
    • The expression displays “@activity(‘Lookup List’).output”
  • Append “.value” to the express so that the expression reads “@activity(‘Lookup List’).output.value”

Click the Finish button.

Click the “ForEach Value” activity’s Activities edit icon (pencil) to open the ForEach activity’s inner activities canvas. Drag an If Condition onto the canvas and rename it “If Value Is Two”:

On the “If Value Is Two” If condition’s Activities tab, configure the Expression property’s dynamic content, setting it to @equals(item().value, 2):

Click the Finish button.

Click the “Break Foreach” pipeline name in the breadcrumbs:

Click the pipeline Variables tab and add a new String variable named PipelineRunId:

Drag a Set variable activity onto the pipeline canvas and rename it “Set PipelineRunId”. On the Variables tab, select the PipelineRunId variable from the Name dropdown. Click the “Add dyanmic content [Alt + P]” link beneath the Value property and enter an expression similar to:

@concat(‘https://management.azure.com/subscriptions/<subscription id>/resourceGroups/<resource group>/providers/Microsoft.DataFactory/factories/’,pipeline().DataFactory,’/pipelineruns/’,pipeline().RunId,’/cancel?api-version=2018-06-01′)

A Couple / Three Notes…

Note 1: The expression listed above builds a call to the Azure Data Factory REST API. Specifically, to the Cancel method for Pipeline Runs. You must supply the portions shown in italics for:

• Your subscription Id
• Your Resource Group name

The expression will fill in the blanks for your data factory name and the RunId value for the pipeline’s current execution.

Note 2: By default, Azure Data Factory is not permitted to execute ADF REST API methods. The ADF managed identity must first be added to the Contributor role. I describe the process of adding the ADF managed identity to the Contributor role in a post titled Configure Azure Data Factory Security for the ADF REST API.

Note 3: When running in Debug, pipelines may not be cancelled. Pipelines must be triggered (manual triggers work) to be accessible to the REST API’s Pipeline Runs cancel method.

Navigate to the “ForEach Value” activities > “If Value Is Two” True activities. Add a Web activity to the canvas and rename the web activity “Break Out”. On the Settings tab, click inside the URL property, click the “Add dynamic content [Alt + P]” link, and then select Variables > PipelineRunId. Select “POST” for the Method property. The Body property displays; enter a message key-value in JSON (similar to {“message”:”Cancelling the pipeline”}). Expand the Advanced section and select the MSI option. When the Resource property displays, enter “https://management.azure.com” in the property value textbox:

Click the Publish All toolbar item if it is enabled (and includes a number-bubble).

Click the Add Trigger > Trigger Now option to trigger the last-deployed-version of the pipeline. If all goes as planned, the pipeline execution should cancel itself. On the ADF Monitor page, a cancelled pipeline execution appears as shown here:

Clicking the “Break ForEach” link in the Pipeline Name column displays runtime details:

Note the “If Value Is Two” Conditional If activity executed three times – once each for the values 0, 1, and 2 – before cancelling the pipeline in the “Break Out” web activity. Note the ForEach activity stopped iterating, and the value 3 was never evaluated because the pipeline was cancelled when iteration reached the value 2.

Mission Accomplished

What did we do?

1. We populated a pipeline variable (PipelineRunId) with a URL that invokes the cancel pipeline runs method of the ADF REST API.
2. We loaded a dataset via a T-SQL query – executed inside a Lookup activity – against a table named dbo.List.
3. We iterated the values returned to the Lookup activity.
   1. We used an If Condition activity to check for a value of 2.
      1. If we found the value 2, we executed the ADF REST API invocation of the cancel pipeline runs method contained in the pipeline variable named PipelineRunId.

Conclusion

I can hear some of you thinking, “But Andy, what if I want to other operations after deciding I want to break out of the ForEach activity but before the pipeline is cancelled?” That’s an excellent question. The answer is: “There’s a book and a class that covers that particular design pattern, but neither have been released (at the time of this writing…).”

<ShamelessPlug>

Need Help Getting Started with ADF?

Enterprise Data & Analytics specializes in training and helping enterprises modernize their data engineering by lifting and shifting SSIS from on-premises to the cloud. Our experienced engineers grok enterprises of all sizes. We’ve done the hard work for large and challenging data engineering enterprises. We’ve earned our blood-, sweat-, and tear-stained t-shirts. Reach out. We can help.

</ShamelessPlug>