0

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?

Product Info

Hadi
  • 36,233
  • 13
  • 65
  • 124
Chadwick
  • 47
  • 1
  • 5

2 Answers2

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:

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
1

It is easy enough to get a count of duplicate PRODID values.

Use an Aggregate transform using

Column PRODID - Operation Group By
Column (*) - Operation Count all

Follow it with a Conditional Split where [Count all] > (DT_UI8)1.

If there are any records in the output, then there was one or more duplicates.

lit
  • 14,456
  • 10
  • 65
  • 119
  • Hello @lit! Thank you for the response! I already did that the conditional split where I count all the duplicates I insert it in a destination table with the productid/productcode column and the count column. But another question how can I add the productlabel in the result? – Chadwick Mar 06 '19 at 08:50
  • What is a `productlabel`? Do you mean `ProductName`? – lit Mar 06 '19 at 13:53
  • Hi @lit. Yes productname. :) – Chadwick Mar 07 '19 at 03:25