SSIS tips: How to use Foreach Loop Container

---

Introduction

When building dynamic ETL workflows in SSIS, the Foreach Loop Container is one of the most flexible tools at your disposal. It allows you to repeat a task for each item in a collection, such as files, database records, or XML nodes.

This is especially useful for automation tasks that require processing multiple inputs without requiring hardcoding.

---

Common use cases

• Loop through files in a folder (e.g., daily JSON logs)
• Process rows from a SQL query one by one
• Iterate over a list of server names or connection strings
• Dynamically update variables during each iteration

---

Enumerator types supported in SSIS

Here are the main enumerator types available with the Foreach Loop Container:

• Foreach File Enumerator
  Iterate over files using wildcards (e.g. *.json)

• Foreach ADO Enumerator
  Loop over the result set of a SQL query

• Foreach Item Enumerator
  Provide a static list of values manually

• Foreach NodeList Enumerator
  Iterate over nodes in an XML document

• Foreach SMO Enumerator
  Work with SQL Server objects, such as databases and tables.

---

Want a real-world example?

If you're looking for a detailed walk-through on using the Foreach ADO Enumerator to loop through JSON files dynamically using the Advanced File System Task, we've got you covered.

👉 Check out the complete guide here