Fuzzy lookup Transformation

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 records with at least one matching record, and returns records with no matching records. In contrast, the Fuzzy Lookup transformation uses fuzzy matching to return one or more close matches in the reference table.

Step 1:

Create a fuzzy lookup source and fuzzy lookup reference tables.

create table fuzzyLookupSource

(

      firstName varchar(10),

      LastName varchar(10),

      BirthDate datetime

)

insert into fuzzyLookupSource

select 'Masud','Pervez','02/07/1980' union all

select 'Tamirul','Islam','03/31/1983' union all

select 'Animesh','Chandra','04/09/1980'  union all

select 'Shahriar','Bin Elahi','05/05/1980'  union all

select 'Masud','Rana','04/15/1980' 

GO

create table fuzzyLookupReference

(

      firstName varchar(10),

      LastName varchar(10),

      BirthDate datetime

)

insert into fuzzyLookupReference

select 'Masud','Parvez','02/07/1980' union all

select 'Tamirul','Islam','03/31/1983' union all

select 'Animesh','Chandra De','06/05/1980'  union all

select 'Shahriar','Elahi','04/09/1980'

GO

Step 2:

Click Windows button and go to SQL Server 2008 R2 and run as a administrator.




It shows a bellow window.








Step 3:



Go to a views and create a new project (Short cut of New Project is Ctrl + Shift +N).





Step 4:

Give a project name (e.g. IS) and click OK button and then shows a bellow window.



Step 5:

To drag and drop the Data Flow Task






Step 6: 




To edit the Data Flow Task then it open a Data Flow









Step 7:


Drag and drop OLEDB source and edit it.








Step 8:

Give a connection manager and select table as dbo.fuzzyLookupSource.





Step 9:
Drag and drop a Fuzzy Lookup transformation to the Data Flow and right click to edit it.








Step 10:

Select a reference table as  dbo.fuzzyLookupSourceReference.





Step 11:

Go to columns and select which columns we want.






Step 12:

In advance set similarity threshold is 50%





Step 13:

Drag and drop conditional split and edit it.





Step 14:

Conditions

 "Solid Matched"  is "_Similarity > 0.85 &&_Confidence > 0.8" 


 "Likely Matched" is "_Similarity > .65 && _Confidence > 0.75"



Step 15:

Drag and drop three derived columns and connection it





Step 16:

Drag and drop union all


Step 17:


Drag and drop data reader destination





Step 18:

Execute a task