You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy
Lookup transformation.
The reference data to be used in the transformation does not change.
You need to reuse the Fuzzy Lookup match index to increase performance and reduce
maintenance.
What should you do?
A.
Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation
Editor.
B.
Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
C.
Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
D.
Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
E.
Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
Explanation:
http://msdn.microsoft.com/en-us/library/ms137786.aspx
The correct answer is option B: the ‘GenerateNewIndex’ option on the “Reference Table” tab of the Fuzzy Lookup Transformation Editor.
There’s no ‘GenerateAndPersistNewIndex’ option in the Fuzzy Lookup Transformation, even when you access the Advanced Editor Window.
Reference: 70-463 Exam Study Guide, Ch 20, Pg 760: Identity-Mapping and De-Duplication
When the package first runs the transformation, it copies the reference table, adds 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. When the package runs again, the transformation can either use an existing match index or create a new index.
If the reference table is static, the package can avoid the potentially expensive process of rebuilding the index for repeat sessions of data cleaning.
Sorry for the misinformation, guys. Option A is very correct:
GenerateAndPersistNewIndex
A