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

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

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

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

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

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

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

Lets go to Lookup transformation Advanced Editor.

Lookup Transform Advanced Editor- Connection Managers

Below 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

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


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • SSIS- Derived Column Transform

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

  • SSIS- Aggregate Transform

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

  • SSIS- Union All Transform

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

  • SSIS- Lookup Transform

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

  • SSIS- Merge Join Transform

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

  • SSIS- Conditional Split Transform

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

  • SSIS- Multicast Transform

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