SSIS-Fuzzy lookup for cleaning dirty data

Fuzzy Lookup

Introduction

Cleansing data before it is stored in a reporting database is necessary to provide value to consumers of business intelligence applications. Fuzzy logic is an approach to computing based on "degrees of truth" rather than the usual "true or false" (1 or 0) Boolean logic on which the modern computer is based.

First create the database and table from this script.

-- Create database

create database fuzzyLookup

go

use fuzzyLookup

go

create table fuzzyLookupSource

(

      firstName varchar(10),

      LastName varchar(10),

      BirthDate datetime

)

insert into fuzzyLookupSource

select 'Vivek','Tiwari','02/07/1982' 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 'Vivek','Tiwari','02/07/1982' 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

Then open SQL Server Business Intelligence Development Studio.

Then go to File->New->Project and select Integration Service Project.

Select "Data Flow Task" from "Control Menu" and Drag it on "Control Flow" tab. Then double click it.

Click Connection for new connection or select from existing connection.

Click New button to create new Data Connection or select from left tab.

 Select "Server Name","Authentication" and "Database" which will be "fuzzyLookup" for this example. Click Test Connection for checking then click OK.

Take OLEDB Data source  Select "Table", then click OK.

Select "Fuzzy Lookup" from "Data Flow Transformation" and Drag it on "Data Flow" tab. And connect extended green arrow from “OLE DB Source” to your fuzzy lookup. Double click on “Fuzzy Lookup” task to configure it.

Select "OLE DB Connection" and "Reference Table name" in "Reference Table" tab.

Map Lookup column and Output Column in "Columns tab. Add prefix "Ref_" in output column filed.

Let all value as it is in "Advanced" tab.

Select "Conditional Split" from "Data Flow Transformation" and Drag it on "Data Flow" tab. and connect extended green arrow from “Fuzzy Lookup” to your "Conditional Split". Double click on “Conditional Split” task to configure it.

Create two output. One is "Solid Matched" which Condition is "_Similarity > 0.85 && _Confidence > 0.8" and another is "Likely Matched" which condition is "_Similarity > .65 && _Confidence > 0.75". Click OK.

Select "Derived Column" from "Data Flow Transformation" and Drag it on "Data Flow" tab. and connect extended green arrow from “Conditional Split” to your "Derived Column".

Select Output as "Solid Matched" and click OK.

Double click on “Derived Column” task to configure it.

Select another "Derived Column" from "Data Flow Transformation" and Drag it on "Data Flow" tab. and connect extended green arrow from “Conditional Split” to your "Derived Column 1".

Select Output as "Likely Matched" and click OK.

Double click on “Derived Column 1” task to configure it.

Select another "Derived Column" from "Data Flow Transformation" and Drag it on "Data Flow" tab. And connect extended green arrow from “Conditional Split” to your "Derived Column 2".

Double click on “Derived Column 2” task to configure it.

Select another "Union All" from "Data Flow Transformation" and Drag it on "Data Flow" tab. and connect extended green arrow from “Derived Column” to your "Union All" and “Derived Column 1” to your "Union All" and “Derived Column 2” to your "Union All".

Double click on “Union All” task to configure it.

Select "Flat File Source" from "Data Flow Destination" and Drag it on "Data Flow" tab. and connect extended green arrow from “Union All” to your "Flat File Source".

Double click on “Flat File Destination” task to configure it.

 If you execute the package with debugging (press F5), the package should succeed and appear as shown here:

To check what is happining here:

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.