Azure Synapse Analytics: Introduction To For Each Activity [Introduction to Synapse - Ep. 12]

Azure Synapse Analytics includes several orchestration activities that help data professionals automate common pipeline tasks, and in this video from Pragmatic Works, Mitchell Pearson introduces one of the most useful options: the For Each activity. His walkthrough focuses on how to collect a list of files from Azure Data Lake Storage Gen2 and pass that list into a pipeline activity that can process each file one at a time. Rather than trying to do too much in a single lesson, Mitchell keeps the demonstration focused on setup, configuration, and understanding how iteration works inside Synapse pipelines.

Why the For Each Activity Matters

Mitchell explains that the For Each activity is ideal when a pipeline needs to repeat the same steps across multiple items. In this example, those items are files stored inside a folder. He describes several realistic use cases for this approach, including loading file names into a database table, copying files, running ETL operations, and even triggering notifications after processing. Instead of building separate logic for every file, the For Each activity lets developers define one process and apply it repeatedly to every item in a list.

This type of looping is especially valuable in modern data engineering workflows because file-driven processes are extremely common. A pipeline may receive batches of CSV files, logs, exports, or staging data on a regular basis. By using the For Each activity, Synapse can dynamically handle all available files without requiring hardcoded paths or manual updates.

Preparing the Source Data

To support the demo, Mitchell uses files already stored in an Azure Data Lake Gen2 account. Inside a training container and an internet sales folder, he has a set of files that he wants to work with. The goal is not yet to transform or load the file contents, but to retrieve the file list itself and use it as the driver for the pipeline.

Before building the pipeline, he creates a dataset that points to the correct folder location in the data lake. He chooses a CSV-based integration dataset, connects it to the data lake account, and configures it to reference the folder rather than a specific file. That distinction is important because the Get Metadata activity needs access to the directory in order to return the items inside it. He also sets the dataset options such as header handling and skips schema import since schema details are not required for this specific step.

Building the Pipeline

Once the dataset is ready, Mitchell moves into the Integrate hub to create a new pipeline. The first activity added is Get Metadata. This activity is responsible for reading the folder and returning a list of child items. In the settings, he points the activity to the dataset created earlier and selects child items from the field list. That output becomes the array that the pipeline will loop through.

Next, he adds the For Each activity and connects it to the Get Metadata activity using a success dependency. This means the loop will only start after the metadata retrieval completes successfully. In the For Each settings, he selects the items property and uses dynamic content to reference the output of the Get Metadata activity. Specifically, he chooses the child items output, which represents the array of files in the folder.

How Iteration Works

Mitchell emphasizes that the For Each activity does not do anything by itself until an inner activity is added. To keep the lesson simple and avoid turning the video into a long end-to-end demo, he places a basic Wait activity inside the loop. This serves as a placeholder so the pipeline can execute once for every file and demonstrate that the iteration works correctly.

He notes that if there are 11 files in the folder, the inner activity should run 11 times. This is an effective way to validate the loop structure before adding more complex business logic.

Referencing the Current Item

One of the most useful parts of the lesson is Mitchell’s explanation of how to reference the current file being processed. He briefly introduces the idea using a stored procedure example, even though he saves the full implementation for the next video. Inside the loop, Synapse exposes the current row or current item, and developers can reference properties from that item using dynamic expressions.

In his example, the expression would target the current item and then specify a property such as name. This is how a pipeline can pass the active file name into another activity, such as a stored procedure or logging process. Mitchell also explains that the exact property names depend on the structure of the returned object. To discover those properties, users can run the pipeline in debug mode and inspect the output of the Get Metadata activity.

Using Debug Mode to Validate Results

After configuring the pipeline, Mitchell runs it in debug mode. This allows him to confirm that the Get Metadata activity executes first, followed by the For Each activity. He then checks that the inner activity runs once for each file found in the folder. By reviewing the pipeline output, users can also inspect the child items array and verify which attributes are available for each item, such as name and type.

Final Takeaway

Mitchell Pearson presents a practical and beginner-friendly introduction to the For Each activity in Azure Synapse Analytics. Through this Pragmatic Works lesson, viewers learn how to create a folder-based dataset, retrieve file lists with Get Metadata, pass those results into a For Each loop, and validate execution through debug mode. Most importantly, Mitchell shows that understanding the structure of activity outputs is the key to building dynamic, reusable pipelines. This lesson sets the stage for more advanced examples where the loop can drive stored procedures, data movement, and end-to-end automation.

 Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Synapse and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.