SQLTeam.com | Weblogs | Forums

SSIS/BIDS exclude duplicates from import


I have a package that imports a flat file into a staging table. I want to set up my package so that it will find duplicates on import. A unique record is identified by a combination of a store number and SS#. So I set up an aggregate transformation that will group by both of those columns and also count the SS#. I then created a conditional split that will write only the unique records to my staging table and write the duplicates to another table.

The problem is that only the columns that I am grouping by or counting are available in the conditional split.

How can I identify duplicates on import based on only the combination of two columns being the same but copy all of the columns to my staging table?

I may end up doing this by writing SQL code that will only copy the unique records to the final destination table once they are in the staging table but I would like to know if this can be done in BIDS.



you can use a Sort transformation


Thanks Glen. I tried that and that will work for excluding the records. But it does not identify the ones that are duplicated. I wonder if I need the sort after a conditional split and a SQL task to identify the duplicates to write to the other table.


There's a check box in the Sort Transformation dialog to only send unique rows into the pipeline