Redirect
Duplicate Rows Using SSIS
There
are many ways to redirect the duplicate into another direction. You can use
Script Component, Lookup Transformation etc. I am showing you the simplest way
to redirect the duplicate. For this, here I am using Fuzzy Group Transformation to
redirect the duplicate into another direction.
Here, I
am taking Repeat_Data table from my Employee Database. In this table I have
created some duplicate records for better understand.
Now Go
to BIDS, Start New projects, Name the Project.
Take Data flow Task
and double click on it.
Take OLEDB Data Source,
double click on it and configure it.
Choose
Table Repeat_Data.
Go to Columns and take all columns.
Take Fuzzy Group
Transformation and double Click on it. A Fuzzy Group Transformation editor
will open. Leave the first tab that is Connection Manager . Click the second
tab i.e. Columns
and configure it as directed below.
Make the Minimum Similarity of
name column to 0.25.
Go to Advanced tab
and set the Threshold
to
0.50 and press OK button.
Take Conditional Split Transformation and
double Click on it and write Condition.
Condition: _key_in == _key_out
Make the
Default output
name as Duplicate Rows.
Your all
configuration has completed now you can take any destination where you want to
keep your unique records and where you want to keep your duplicate records.
Here I am taking Multicast Transformation for just showing you the output of
this configuration with the help of Data Viewer.
It is the Preview of your Package.
(Note: It is very easy way to redirect the duplicate into
another direction but it has a drawback. It is a blocking transformation so if
you use it then may be you have some performance
issue.)
