Fuzzy Lookup Transformation
The Fuzzy Lookup transformation performs data cleaning tasks such as standardizing data, correcting data, and providing missing values.
The Fuzzy Lookup transformation differs from the Lookup transformation in its use of fuzzy matching. The Lookup transformation uses an equi-join to locate matching records in the reference table. It returns either an exact match or nothing from the reference table. In contrast, the Fuzzy Lookup transformation uses fuzzy matching to return one or more close matches from the reference table.
A Fuzzy Lookup transformation frequently follows a Lookup transformation in a package data flow. First, the Lookup transformation tries to find an exact match. If it fails, the Fuzzy Lookup transformation provides close matches from the reference table.
The transformation needs access to a reference data source that contains the values that are
Prerequisite :
1) Source table and you should know the column on which you want to do the fuzzy lookup.
2) Make destination table by using figure 7 by using New button.
To create a new SSIS package, follow these steps:
- Launch Business Intelligence Development Studio.
- Select File > New > Project.
- Select the Business Intelligence Projects project type.
- Select the Integration Services Project template.
- Select a convenient location.
- Name the new project ISProject1 and click OK.
Figure - 1
Figure - 2
Figure - 3
Figure - 4
Drag and drop column on which you want to do fuzzy lookup and select the columns which you required in destination table.
Figure - 5
Figure - 6
Create new table by using New button
Figure - 7
Make sure whether all the columns are properly mapped. (Otherwise you will get the values NULL in unmapped columns)
Figure - 8
Execute the package
Figure - 9
Checklist :
1. Right click on Fuzzy lookup รจ Properties (Make sure Exhaustive property is false )
By : Shagaf Khot
No comments:
Post a Comment