How to: Implement a Lookup Transformation in Full Cache Mode (SQL Server Video)

Video Summary

This video demonstrates how to implement a Lookup transformation in Full Cache mode by using a Lookup transformation, Cache connection manager, and reference data set that is stored in cache.

Video Transcript

Hello, my name is Carla Sabotta. I write documentation for the Microsoft SQL Server Integration Services product.

In this video, I’m going to show you how to implement a Lookup transformation in Full Cache mode. You’ll learn how to implement a lookup in a package by using a Lookup transformation, Cache connection manager, and reference data set that is stored in cache.

You access additional information in a related data source by performing a lookup. The lookup maps a value in an input data source to a value in the related data source based on a common column and returns the matching rows. The related data source is referred to as a reference dataset.

The first step in implementing a Lookup in a package is to add two Data Flow tasks to the Control Flow tab. One data flow task will contain the Cache Transform transformation and the Cache Connection Manager. The other data flow task will contain the Lookup Transformation.

The annotations that we’ve added to the data flow tasks briefly document what the tasks do. Annotations make it easier to understand and maintain a package.

You connect the data flows using a precedence constraint to ensure that the Cache Transform executes before the Lookup Transformation executes; otherwise the package will fail.

In the first data flow task, you add the Cache Transform transformation, and then connect the transformation to a data source that will generate a reference dataset for the Lookup Transformation. In this demonstration, the Cache Transform connects to an OLE DB Source that connects to a SQL Server table (Production.Product).

Next, you configure the Cache Transform to connect to a Cache Connection Manager and write the data from the connected data source to the Cache Connection Manager.

Now, you configure the Cache Connection Manager to specify the following:

• Which columns in the reference dataset are the Index columns?

  The Lookup Transformation maps columns in an input data source only to index columns in the reference dataset. You must specify at least one column as the index column.

  In this demonstration, we’ll specify the productID column because this is the common column between the input data source and the reference dataset.

• Is the reference dataset that is stored in an in-memory cache while the package runs, persisted to a file?

  Saving the cache to a file and then using the file instead of a Cache Transform to write data to cache connection managers, enables you to share the cache between multiple Lookup Transformations in the different packages and to improve the performance of the lookup operation.

The next step is to add the Lookup Transformation to the second data flow and connect it to an input data source. In this demonstration, the transformation is connected to an OLE DB Source component and the source is connected to a SQL Server table (Sales.SalesOrderDetail).

Now, you configure the Lookup Transformation to specify the following:

• The reference dataset is cached before the Lookup Transformation executes (select Full cache options).

• The Lookup Transformation uses a Cache connection manager to connect to the reference dataset (select Cache connection manager option).

• Select a Cache connection manager, in this case the connection manager that we added to the first data flow.

• Map a column from the input data source to an index column in the reference dataset, to create a join between the two tables.

  In this demonstration, we map the productID column that is in both the input data source and the reference dataset.

• Select one or more columns in the reference dataset in which you want to look up values.

  In this case, we’ll look up the Name value. When the package runs, the Lookup Transformation will return rows with this value, where the productID in the input data source matches the productID in the reference dataset.

• Specify whether the values in the reference dataset columns replace values in the input data source columns or are added as new columns.

  In this case, we’ll specify that a new column is created for the Name value.

We’ll connect the Lookup transformation to an OLE DB Destination and add a data viewer to view the results of the Lookup operation.

Now, we’re ready to run the package.

As you can see, the Lookup returned matching rows with the name column value.

This video demonstrated how to implement a lookup in a package by using a Lookup transformation, Cache connection manager, and reference data set that is stored in cache. The video also demonstrated how to add a data viewer that displays the results of the Lookup operation.

Thank you for watching this video. We hope that you have found this of value, and will return to the Web site to view other Microsoft SQL Server videos.