Fuzzy Lookup Transformation Editor (Reference Table Tab)

Updated: 5 December 2005

Use the Reference Table tab of the Fuzzy Lookup Transformation Editor dialog box to specify the source table and the index to use for the lookup.

The Fuzzy Lookup transformation creates a working copy of the reference table. The indexes described below are created on this working table by using a special table, not an ordinary SQL Server index. The transformation does not modify the existing source tables unless you select Maintain stored index. In this case, it creates a trigger on the reference table that updates the working table and the lookup index table based on changes to the reference table.

The Exhaustive and the MaxMemoryUsage properties of the Fuzzy Lookup transformation are not available in the Fuzzy Lookup Transformation Editor, but can be set by using the Advanced Editor. In addition, a value greater than 100 for MaxOutputMatchesPerInput can be specified only in the Advanced Editor. For more information on these properties, see the Fuzzy Lookup Transformation section of Transformation Custom Properties.

To learn more about the Fuzzy Lookup transformation, see Fuzzy Lookup Transformation.

OLE DB connection manager

Select an existing OLE DB connection manager from the list, or create a new connection by clicking New.


Create a new connection by using the Configure OLE DB Connection Manager dialog box.

Generate new index

Specify that the transformation should create a new index to use for the lookup.

Reference table name

Select the existing table to use as the reference (lookup) table.

Store new index

Select this option if you want to save the new lookup index.

New index name

If you have chosen to save the new lookup index, type a descriptive name for the index.

Maintain stored index

If you have chosen to save the new lookup index, specify whether you also want SQL Server to maintain the index.

Use existing index

Specify that the transformation should use an existing index for the lookup.

Name of an existing index

Select a previously created lookup index from the list.

Release History

5 December 2005

New content:
  • Added Note about use of the Advanced Editor.