Edit

Lesson 2: Add looping with SSIS

  • Article

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

See also

For Loop container