Lookup Transformation In SSIS

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

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.

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.

You can configure the Lookup transformation in the following ways:

 

Click on source data flow task , specify the source database and table.

Columns used in the source table.

Then click on Look Up the following window will pop up. Here you have to mention in ‘specify how to handle the rows with no matching entries’ as ‘Redirect rows to no match output’.

Specify connection to the reference dataset.

Specify two columns to match the Lookup condition. one from source table and another from Lookup source table.

Then click on destination data flow task, select destination database and table.

Map the columns in the Lookup and columns on the destination table. It will copy all matched records. Match output. The match output handles the rows in the transformation input that match at least one entry in the reference dataset.

 Now click on Lookup No match output destination dataflow task, select destination database and table.

Map the columns in the Lookup and columns on the destination table. Here it will copy all the records those they are not matched with the source table in Lookup. 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

 Lookup operation after execution .In this example, all 16 matched records are populated to Lookup Match Output and other 10 unmatched records are populated to Lookup No Match Output .