MatchIndexOption property in Fuzzy look in SSIS package

When we run the package first runtime, the Fuzzy loop up transformation copies the reference table, add a key with an integer data type to the new table and builds an index on the key column. Next, the transformation builds an index, called a match index, on the copy of the reference table. The match index stores the results of tokenizing the values in the transformation input columns, and the transformation then uses the tokens in the lookup operation. The match index is a table in a SQL Server database.

Based on the package design we can set MatchIndexOption in our package. 

GenerateAndMaintainNewIndex

Create a new index, save it, and maintain it. The transformation installs triggers on the reference table to keep the reference table and index table synchronized.

GenerateAndPersistNewIndex

Create a new index and save it, but do not maintain it.

GenerateNewIndex

Create a new index, but do not save it.

ReuseExistingIndex

Reuse an existing index.

The GenerateAndMaintainNewIndex option installs triggers on the reference table to keep the match index table and the reference table synchronized. If we have to remove the installed trigger, we must run the sp_FuzzyLookupTableMaintenanceUnInstall stored procedure, and provide the name specified in the MatchIndexName property as the input parameter value.

We should not delete the maintained match index table before running the sp_FuzzyLookupTableMaintenanceUnInstall stored procedure. If the match index table is deleted, the triggers on the reference table will not execute correctly. All subsequent updates to the reference table will fail until you manually drop the triggers on the reference table.

We will get this SP on the database in SYSTEM SP