Transformation Service Question About De_dup Process.

A standard SQL statement that is used a lot in SQL is a left join.



What I'm trying to do is dedup a table.



Example:



Select * from table1 as t1

left join table2 as t2

on t1.firstname = t2.firstname

and t1.lastname = t2.lastname

and t1.gender = t2.gender

and t1.dob = t2.gender

where t2.lastname is null



What I thought was with in a data flow I have one OLDBD SQL statement
that is the full table and in the second table I have the OLDBE SQL
statement deduping the dups. What I am wanting to do next is remove the
dups and then rejoin the deduped dups back into the oringal table that
was less dups.





In the data flow I have:



OLeDB source: Original Table

OLEDB Sounce 1: Deduped Dups

Sort both OLEDB connections

on the OLEDB Dups connection I put a multicast

Then place the both flows into a Merge Join that I have setup as a left
outer join select all columns from the oringal table linking to the
right table on firstname, lastname, dob, gender. But no columns from
the right table is selected.



Then I have the merge join go into a conditional spilt to seperate out
the NULL values and choose the error out. Redirecting the data through
the red arrow into a union all will the other multicast dups. Then into
OLEDB destination tables.



I only get the dedupped dups throught the process what is the best way
to do this with Tranformations. Maybe I'm not using the merge join
correctly or the conditional split please help.



Thanks.