• Article

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 reference data source must be a table in a SQL Server 2000 or later database.

Note

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.

Note

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.

Options

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

<div class="alert">


> [!NOTE]
> <P>When you select <STRONG>Maintain stored index</STRONG> on the <STRONG>Reference Table</STRONG> tab of the <STRONG>Fuzzy Lookup Transformation Editor</STRONG>, the transformation uses managed stored procedures to maintain the index. These managed stored procedures use the common language runtime (CLR) integration feature in SQL Server 2008. By default, CLR integration in SQL Server 2008 is not enabled. To use the <STRONG>Maintain stored index</STRONG> functionality, you must enable CLR integration. For more information, see <A href="ms131048(v=sql.100).md">Enabling CLR Integration</A>.</P>
> <P>Because the <STRONG>Maintain stored index</STRONG> option requires CLR integration, this feature works only when you select a reference table on an instance of SQL Server 2005 or SQL Server 2008 where CLR integration is enabled. The index cannot be maintained on an instance of SQL Server 2000.</P>


</div>

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

Change History

Updated content

  • Identified the versions of SQL Server for which the Maintain stored index option works.