Lesson 2: Add looping with SSIS
Applies to: 
SQL Server
SSIS Integration Runtime in Azure Data Factory
In Lesson 1: Create a project and basic package with SSIS, you created a package that extracts data from a single flat file source. The data is then transformed using Lookup transformations. Finally, the package loads the data into a copy of the FactCurrencyRate fact table in the AdventureWorksDW2022 sample database.
An extract, transform, and load (ETL) process typically extracts data from multiple flat file sources. Extracting data from multiple sources requires an iterative control flow. Microsoft Integration Services can easily add iteration or looping to packages.
Integration Services provides two types of containers for looping through packages: the Foreach Loop container and the For Loop container. The Foreach Loop container uses an enumerator for the looping, while the For Loop container typically uses a variable expression. This lesson uses the Foreach Loop container.
The Foreach Loop container enables a package to repeat the control flow for each member of a specified enumerator. With the Foreach Loop container, you can enumerate:
ADO recordset rows
ADO .Net schema information
File and directory structures
System, package, and user variables
Enumerable objects in a variable
Items in a collection
Nodes in an XML Path Language (XPath) expression
SQL Server Management Objects (SMO)
In this lesson, you modify Lesson 1's example ETL package to use a Foreach Loop container, and set a user-defined package variable for the package. That variable is then used to iterate through the matching files in the sample folder.
In this lesson, you won't modify the data flow, only the control flow.
Note
If you haven't already, see the Lesson 1 prerequisites.
Lesson tasks
This lesson contains the following tasks:
Start the lesson
Step 1: Copy the Lesson 1 package