Example 3: Lookup

This example builds on the previous example. A transformation is added to the data flow using the SalesPersonID from each aggregate record to retrieve the first and last name of the salesperson. The sales person name is appended to the aggregate record.

  1. Create a copy of Example2.dtsx following the instructions at the beginning of Example 2: Aggregation. Rename it Example3.dtsx.

  2. Open the Example3 package and switch to the Data Flow designer.

  3. Delete the data flow output from the Sum Amounts aggregate to the Sales Order Destination by right-clicking on it and selecting Delete from the context menu.

  4. Drag a Lookup transformation onto the designer to the right of the Sum Amounts aggregate item. A Lookup transformation locates data in a reference data source based on data in the input columns, and appends that data to its data output. Rename the Lookup transformation to Lookup Sales Person.

  5. Click on the Sum Amounts item. Drag the data flow output arrow to the Lookup Sales Person transformation.

  6. Double-click the Lookup Sales Person transformation to launch the Lookup Transformation Editor dialog shown in the following figure.

    Lookup Transformation Editor dialog

    Figure 34. Lookup Transformation Editor dialog

  7. Create a new OLE DB connection manager as the data source for Lookup Sales Person by clicking the New... button next to the OLE DB connection manager dropdown. This opens the Configure OLE DB Connection Manager ...

Get SQL Server Integration Services now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial