SSIS/BIDS exclude duplicates from import

1

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.

Thanks,
Scott

2

you can use a Sort transformation

3

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.

4

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