SQL Server Integration Services

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.

   

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