Fuzzy Lookup Transformation Editor (Reference Table Tab)

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

See Also


Fuzzy Lookup Transformation Editor (Columns Tab)
Fuzzy Lookup Transformation Editor (Advanced Tab)

Other Resources

Integration Services Error and Message Reference

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

5 December 2005

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