Lesson 2: Adding Looping

In Lesson 1: Creating the Project and Basic Package, you created a package that extracted data from a single flat file source, transformed the data using Lookup transformations, and finally loaded the data into the FactCurrencyRate fact table of the AdventureWorksDW sample database.

However, it is rare for an extract, transform, and load (ETL) process to use a single flat file. A typical ETL process would extract data from multiple flat file sources. Extracting data from multiple sources requires an iterative control flow. One of the most anticipated features of Microsoft Integration Services is the ability to 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 to perform the looping, whereas the For Loop 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 and schema information

• File and directory structures

• System, package and user variables

• SQL Server Management Objects (SMO)

In this lesson, you will modify the simple ETL package created in Lesson 1 to take advantage of the Foreach Loop container. You will also set user-defined package variables to enable the tutorial package to iterate through all the flat files in the folder. If you have not completed the previous lesson, you can also copy the completed Lesson 1 package that is included with the tutorial.

In this lesson, you will not modify the data flow, only the control flow.

Important

This tutorial requires the AdventureWorksDW sample database. For more information about how to install and deploy AdventureWorksDW, see Considerations for Installing SQL Server Samples and Sample Databases.

Lesson Tasks

This lesson contains the following tasks:

• Task 1: Copying the Lesson 1 Package

• Task 2: Adding and Configuring the Foreach Loop Container

• Task 3: Modifying the Flat File Connection Manager

• Task 4: Testing the Lesson 2 Tutorial Package

Start the Lesson

Task 1: Copying the Lesson 1 Package

Stay Up to Date with Integration Services For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet: Visit the Integration Services page on MSDN Visit the Integration Services page on TechNet For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Concepts

For Loop Container