SQL Server Integration Services

Example 7: Looping and Control Flow

This example introduces looping. The example uses a text file containing multiple sales person ID values, loops through the collection, retrieves Sales.SalesOrderHeader records for each sales person ID, and writes those records to a separate flat file for each sales person ID.

1. Create a text file named SalesPersonList.txt, which will contain the list of SalesPersonID values (275, 280, and 286) from which to extract records from Sales.SalesOrderHeader. The file is shown in the following figure.

   

   Figure 50. SalesPersonList.txt

2. Create a new package and name it Example7.dtsx. Open the Example7 package and select the Code Flow designer.

3. Drag a Data Flow task onto the Code Flow designer. The Data Flow task will read the collection of SalesPersonID values from the flat file into a Recordset Destination. This is a "Data flow Destination" that creates and populates an in-memory ADO recordset. Rename the Data Flow Task to Get Sales Person List.

4. Create a variable scoped at the package level to hold the recordset object containing sales persons read from the flat file. Right-click the Control Flow designer, and select Variables from the context menu to open the Variables pane. Add a new variable. Set its Name property to "rsSalesPersonList" and set its Data Type property to Object. The completed Variables pane is shown in the following figure.

   Figure 51. Variables ...