Column value should be unique to another column value

Question

I'm creating an SSIS package wherein a user needs to be notified if there is a duplicate productid for one productname. We retrieve the products thru a csv file being sent to us in a shared location.

In this example I have ProductID = 1 for both products chair and lamp.

So basically the productID = 1 was a duplicate because it was also used by the product lamp. Hence, notify users for the duplicate ProductIDs used.

Can someone give me tips on how to do this?

Answer 1

You can do this in 3 approaches:

1. Insert data into a staging table and use a query similar to:

   SELECT PRODUCTID, COUNT(*) 
   FROM TEMPTABLE
   GROUP BY PRODUCTID
   HAVING COUNT(*) > 1
   

2. Use a Script component to count occurrences and throw exception when duplicates found. Check the following link for more information:

   • How to get desired output in SSIS by grouping by and count?

3. Use aggregate transformation and apply count with group by on ProductID, check the following link fro more information:

   • SSIS - Perform group by and count on flat file