O'Reilly logo

SQL Server Integration Services by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

    SalesPersonList.txt

    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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required