Design a mapping to load each product once into one table and the remaining products which are duplicated into another table.

This is the flow of transformations you need to follow,

First you need to sort the data,for this use sorter.

In expression transformation you need to create following ports,

Here FIELD9 is the column name which contains product key for me.

for v_count you need to use following condition,

The output from this expression will be in the form of,

Product, O_count
A, 1
B, 1
B, 2
B, 3
C, 1
C, 2
D, 1

Now use a router transformation using group condition as,

O_count=1.

Then all unique rows will go to one target and connect the default group to another table which will hold the duplicates.