SSIS Remove Duplicate Rows Using Fuzzy Grouping

-
Source data may have the duplicate rows which needs to be removed as part of data cleansing task.
Fuzzy Grouping is one of the transformation in Data Flow Transformations which can be used to group the similar rows in the source stream line. This transformation performs data cleaning tasks by identifying rows of data that are likely to be duplicate.
Lets generate some of duplicate records as shown in below screen:-
Source Query
Create one DataFlow task inside which Drag OLE DB Source component and write the query shown above which will become as source data which has duplicate records.
 OLEDBSource
Drag the Fuzzy Group component and open the editor.
Go to the Columns tab, and check the checkbox agist the column which you want to analyze of similarity.
FuzzyGroupParticipatingColumn
after that go to the advanced tab. Here we can define Similarity threshold.
SimilarityThreshold
Important values in aboe screenshot are _key_in and _key_out in our example as we are removing duplicate records.
 Lets for temporary use drag the Union All component and add dataviewer control to look for the data after passing through FuzzyGroup Transformation.
In below screen, we can see the result.
FuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutputFuzzyGroupOutput
So we can see in above screen , Key_in and Key_out has same value for unique rows. Hence we can take only those records which have Key_in=Key_out.
Remove the Union all and add conditional split transformation to get the unique rows as shown in below screen:-
ConditionalSplit
after that to check that drag Unionall component and add data viewer and run the task.
After running the data flow task it will show below screen:-
FinalResult
Hence in above result we can see Unique rows.

Comments

Post a Comment

Popular posts from this blog

SharePoint 2007 - Simple Task Dashboard

-
Image
example of creating a simple Task Dashboard. This is a pretty basic example of using all OOTB (out of the box) features and combining them to quickly build a solution. Scenario: We want to provide a way for users to easily see a summary of the current project tasks. We want them to be able to see the following information: Open Tasks Assigned to Them Overdue Tasks Tasks Due This Week Task Calendar Tasks Assigned to Others Solution: To build this solution we will be using several different tools available in SharePoint, all included in the list below: Task List Task List Views We will create views for each of the different ways we want to see the data displayed on our dashboard. Web Part Page List View Web Parts Custom Navigation The first thing we will do is create our task lists and then configure all the views that we need. To create the list, go to View All Site Content, Create and then select the Task list. Next, we will need to configure our custom views. To do this, w
Read more

MERGE transformation in SSIS

-
Image
In this post we are gonna discuss about MERGE transformation. MERGE in SSIS is equal to UNION ALL in SQL Server. This transformation unions two datasets/tables.The merge Transformation combines two  sorted  dataset into single dataset. Highlighted the text SORTED in last statement because “It is not possible to use MERGE when the inputs are NOT SORTED”. There is one more transformation which is very similar to this i.e UNION ALL. The Merge Transformation is similar to the union all transformations. Use the union all transformation instead of the merge transformation in the following situations. The transformation inputs are not sorted The combined output does not need to be sorted. The transformation has more than two inputs. MERGE takes ONLY TWO inputs where as UNION ALL can take more than two. Now lets see how to configure MERGE transformation with an example. I created TWO tables with names MergeA and MergeB and inserted few records into each table as shown below.
Read more