Skip to content

lesson-2-adding-looping-with-ssis.md

Latest commit

 

History

History
66 lines (38 loc) · 3.29 KB

lesson-2-adding-looping-with-ssis.md

File metadata and controls

66 lines (38 loc) · 3.29 KB
title Lesson 2: Add looping with SSIS
description Lesson 2: Add looping with SSIS
author chugugrace
ms.author chugu
ms.date 01/03/2019
ms.service sql
ms.subservice integration-services
ms.topic tutorial

Lesson 2: Add looping with SSIS

[!INCLUDEsqlserver-ssis]

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 [!INCLUDE sssampledbdwobject-md] 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. [!INCLUDEmsCoName] [!INCLUDEssISnoversion] can easily add iteration or looping to packages.

[!INCLUDEssISnoversion] 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

  • [!INCLUDEssNoVersion] 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