SSIS- Lookup Transform

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.

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.

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.

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.

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.

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

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

Below Lookup Transform Advanced Editor- Component Properties

Below Lookup Transform Advanced Editor- Input Columns

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