Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
Login
New Account?
Recovery
Go to Login
SSIS

SSIS- Lookup Transform

Updated on Sep 30, 2020

This is a Fast Forward tutorial on Lookup Transformation in Microsoft SQL Server Integration Services.

Lookup Transformation

The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. We use the lookup to access additional information in a related table that is based on values in common join columns. Lookup transformation dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query.

Implementation

In this scenario we want to get the department name and location information from the department table for each corresponding employee record from the source employee table.

Control Flow
Control Flow

Here we have the EMP table as OLEDB Source, next the DEPT table as the Lookup dataset and finally the OLEDB Destination table to stage the data.

Data Flow
Data Flow

Next we double-click the Lookup transformation to go to the Editor. Select the Connection type to OLEDB connection manager. When required the Lookup dataset can be a Cache file.

Cache Mode

There are three types of caching options available to be configured- Full cache, Partial cache and No cache. In case of Full cache, the Lookup transformation generates a warning while caching, when the transformation detects duplicates in the join key of the reference dataset.

Lookup Transformation Editor- General
Lookup Transformation Editor- General

Next we select the OLEDB connection object from the OLEDB connection manager browser. Next we specify the table or view. We can also use the resultant dataset of an SQL statement as Lookup reference as mentioned earlier if required.

Lookup Transformation Editor- Connection
Lookup Transformation Editor- Connection

Next we define the simple equi join condition between the Source Input Columns and the Reference Lookup Available columns. Next we define the Lookup Columns as Output. We can rename or Alias the Reference Lookup column name if required.

Lookup Transformation Editor- Columns
Lookup Transformation Editor- Columns

Next in case of Partial Cache mode we can specify the Cache size here. Also we can modify the Custom query if required.

Lookup Transformation Editor- Advanced
Lookup Transformation Editor- Advanced

Select Ignore failure for Error. 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, if we configure the Lookup transformation to Ignore lookup failure then such rows are redirected to no match output.

Lookup Transformation Editor- Error Output
Lookup Transformation Editor- Error Output

Lookup Output

The Lookup transformation has the following outputs:

  • Match output- It handles the rows in the transformation input that matches at least one entry in the reference dataset.
  • No Match output- It handles rows in the input that do not match any entry in the reference dataset.

As mentioned earlier, if Lookup transformation is configured to treat the rows without matching entries as errors, the rows are redirected to the error output else they are redirected to the no match output.

  • Error output- It handles the error records.

Let's go to Lookup transformation Advanced Editor.

Lookup Transform Advanced Editor- Connection Managers
Lookup Transform Advanced Editor- Connection Managers

Below Lookup Transform Advanced Editor- Component Properties

Lookup Transform Advanced Editor- Component Properties
Lookup Transform Advanced Editor- Component Properties

Below Lookup Transform Advanced Editor- Input Columns

Lookup Transform Advanced Editor- Input Columns
Lookup Transform Advanced Editor- Input Columns

Below Lookup Transform Advanced Editor- Input & Output Properties.

Lookup Transform Advanced Editor- Input & Output Properties
Lookup Transform Advanced Editor- Input & Output Properties

NOTE:

  • The lookups performed by the Lookup transformation are case sensitive.
  • In case of multiple matches in the reference table, the Lookup transformation returns only the first match returned by the lookup query.

Later we will discuss more on Shared Cache and Persistent Cache Lookup transformation. Stay tuned...