Remove duplicate in SSIS package with preference over a column data

Question

I have duplicate rows in data coming from excel sheet. In the SSIS package, I am using Sort transformation where sorting is done in ascending order by the primary key column ID. But before removing the duplicates I want to see if the email column has email with my company's domain. If so, I want other rows removed than the one having this type of email addresses. What should I do? Please refer to the image attached below.

In the data above, I want to remove two rows of John where email address are [email protected]. In Maria's case, I want to remove two rows having email addresses [email protected], hence preserving rows having email addresses of the domain mycompany.com. If there are multiple rows for a user having email addresses of the domain mycompany.com, I want to keep any one row with the domain email address.

Suggest please.

Answer 1

you can do that in sql like Kobi showed, that may be easier. But if you prefer in ssis:

My test data:

Some points:

Conditional split: First you separate rows with mycompany and those without.

Sort and non_mycompany sort: sort both output on id and remove duplicates.

mycompany_multicast: create two copy of rows with mycompany

Merge join: left join rows without mycompany to rows with mycompany. Note the join order, the purpose is to get rows without mycompany and no matching id in rows with mycompany.

Conditional split1: take rows without mycompany and no matching id in rows with mycompany. you can check id from rows with mycompany, if the id is null then the row has no matching in rows with mycompany.

union all: union the final result