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.