Lookup Operations

A Lookup operation ensures that all the references of a table are made correctly, for example, while inserting data in to a fact table, it is required to provide appropriate dimension keys so that the fact data becomes relevant, in order to load the dimension keys, a look up operation on the dimension table will be done to fetch the dimension keys for a fact data.

SSIS supports, regular Lookup as well as Fuzzy Lookup operations via, Lookup Transformation and Fuzzy Lookup Transformation under the Data Flow Task.

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. A lookup table is nothing but a 'lookup' it give values to referenced table (it is a reference) it is used at the run time it saves joins and space in terms of transformations.

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:

1. 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
   
2. If there are multiple matches in the reference table, the Lookup transformation returns only the first match returned by the lookup query.
   

The Fuzzy Lookup transformation differs from the Lookup transformation in its use of fuzzy matching. The Lookup transformation uses an equi-join to locate matching records in the reference table. It returns either an exact match or nothing from the reference table. In contrast, the Fuzzy Lookup transformation uses fuzzy matching to return one or more close matches from the reference table. A Fuzzy Lookup transformation frequently follows a Lookup transformation in a package data flow. First, the Lookup transformation tries to find an exact match. If it fails, the Fuzzy Lookup transformation provides close matches from the reference table.

The transformation needs access to a reference data source that contains the values that are used to clean and extend the input data. The reference data source must be a table in a SQL Server database. The match between the value in an input column and the value in the reference table can be an exact match or a fuzzy match. However, the transformation requires at least one column match to be configured for fuzzy matching. If you want to use only exact matching, use the Lookup transformation instead.