• Article

Lookup Transformation

The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.

The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query. The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset. For more information, see OLE DB Connection Manager and Cache Connection Manager

You can configure the Lookup transformation in the following ways:

  • Select the connection manager that you want to use. If you want to connect to a database, select an OLE DB connection manager. If you want to connect to a cache file, select a Cache connection manager.

  • Specify the table or view that contains the reference dataset.

  • Generate a reference dataset by specifying an SQL statement.

  • Specify joins between the input and the reference dataset.

  • Add columns from the reference dataset to the Lookup transformation output.

  • Configure the caching options.

The Lookup transformation supports the following database providers for the OLE DB connection manager:

  • SQL Server

  • Oracle

  • DB2

The Lookup transformation tries to perform an equi-join between values in the transformation input and values in the reference dataset. (An equi-join means that each row in the transformation input must match at least one row from the reference dataset.) If an equi-join is not possible, the Lookup transformation takes one of the following actions:

  • If there is no matching entry in the reference dataset, no join occurs. By default, the Lookup transformation treats rows without matching entries as errors. However, you can configure the Lookup transformation to redirect such rows to a no match output. For more information, see Lookup Transformation Editor (General Page) and Lookup Transformation Editor (Error Output Page).

  • If there are multiple matches in the reference table, the Lookup transformation returns only the first match returned by the lookup query. If multiple matches are found, the Lookup transformation generates an error or warning only when the transformation has been configured to load all the reference dataset into the cache. In this case, the Lookup transformation generates a warning when the transformation detects multiple matches as the transformation fills the cache.

The join can be a composite join, which means that you can join multiple columns in the transformation input to columns in the reference dataset. The transformation supports join columns with any data type, except for DT_R4, DT_R8, DT_TEXT, DT_NTEXT, or DT_IMAGE. For more information, see Integration Services Data Types.

Typically, values from the reference dataset are added to the transformation output. For example, the Lookup transformation can extract a product name from a table using a value from an input column, and then add the product name to the transformation output. The values from the reference table can replace column values or can be added to new columns.

The lookups performed by the Lookup transformation are case sensitive. To avoid lookup failures that are caused by case differences in data, first use the Character Map transformation to convert the data to uppercase or lowercase. Then, include the UPPER or LOWER functions in the SQL statement that generates the reference table. For more information, see Character Map Transformation, UPPER (Transact-SQL), and LOWER (Transact-SQL).

The Lookup transformation has the following inputs and outputs:

  • Input.

  • Match output. The match output handles the rows in the transformation input that match at least one entry in the reference dataset.

  • No Match output. The no match output handles rows in the input that do not match at least one entry in the reference dataset. If you configure the Lookup transformation to treat the rows without matching entries as errors, the rows are redirected to the error output. Otherwise, the transformation would redirect those rows to the no match output.

    Note

    In SQL Server 2005 Integration Services (SSIS), the Lookup transformation had only one output. For more information about how to run a Lookup transformation that was created in SQL Server 2005, see Upgrading Lookup Transformations.

  • Error output.

Caching the Reference Dataset

An in-memory cache stores the reference dataset and stores a hash table that indexes the data. The cache remains in memory until the execution of the package is completed. You can persist the cache to a cache file (.caw).

When you persist the cache to a file, the system loads the cache faster. This improves the performance of the Lookup transformation and the package. Remember, that when you use a cache file, you are working with data that is not as current as the data in the database.

The following are additional benefits of persisting the cache to a file:

The following are the caching options:

  • The reference dataset is generated by using a table, view, or SQL query and loaded into cache, before the Lookup transformation runs. You use the OLE DB connection manager to access the dataset.

    This caching option is compatible with the full caching option that is available for the Lookup transformation in SQL Server 2005 Integration Services (SSIS).

  • The reference dataset is generated from a connected data source in the data flow or from a cache file, and is loaded into cache before the Lookup transformation runs. You use the Cache connection manager, and, optionally, the Cache transformation, to access the dataset. For more information, see Cache Connection Manager and Cache Transform.

  • The reference dataset is generated by using a table, view, or SQL query during the execution of the Lookup transformation. The rows with matching entries in the reference dataset and the rows without matching entries in the dataset are loaded into cache.

    When the memory size of the cache is exceeded, the Lookup transformation automatically removes the least frequently used rows from the cache.

    This caching option is compatible with the partial caching option that is available for the Lookup transformation in SQL Server 2005 Integration Services (SSIS).

  • The reference dataset is generated by using a table, view, or SQL query during the execution of the Lookup transformation. No data is cached.

    This caching option is compatible with the no caching option that is available for the Lookup transformation in SQL Server 2005 Integration Services (SSIS).

Integration Services and SQL Server differ in the way they compare strings. If the Lookup transformation is configured to load the reference dataset into cache before the Lookup transformation runs, Integration Services does the lookup comparison in the cache. Otherwise, the lookup operation uses a parameterized SQL statement and SQL Server does the lookup comparison. This means that the Lookup transformation might return a different number of matches from the same lookup table depending on the cache type.

Troubleshooting the Lookup Transformation

A Lookup transformation that has been configured to load none of the reference dataset into cache will fail if a lookup operation matches columns that contain null values. To avoid this failure, manually update the SQL statement to include an OR ISNULL(ColumnName) condition. If a Lookup transformation has been configured to load the reference dataset into cache before the Lookup transformation runs, the lookup operation succeeds.

Note

If possible, avoid using columns that might contain null values in lookup operations. If a column contains null values, configure the Lookup transformation to use an error output that directs rows that have no matching rows in the reference table to a separate transformation output. Alternatively, consider using full caching, which supports lookup operations on null values.

You can log the calls that the Lookup transformation makes to external data providers. You can use this logging capability to troubleshoot the populating of the cache from external data sources that the Lookup transformation performs. To log the calls that the Lookup transformation makes to external data providers, enable package logging and select the Diagnostic event at the package level. For more information, see Troubleshooting Package Execution.

Configuring the Lookup Transformation

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in the Lookup Transformation Editor dialog box, click one of the following topics:

For more information about additional properties that you can set , click one of the following topics:

For more information about how to set properties, click one of the following topics:

External Resources

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.