August 24, 2009 at 10:13 am
Hi all,
I have a SSIS package which loads data from table A to table B, we realized that the table B has some duplicate records (some of the (prod_id) of B occur more than once).
My boss wants me to record all the rows with the duplicate prod_id and save them in an excel sheet. Then mail this excel sheet to some people.
And finally delete the records with these duplicate prod_ids.
Please help,
Thanks
Su
August 24, 2009 at 10:25 am
Did you have a specific question about any of these tasks? You can certainly use SSIS to accomplish what you describe.
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
August 24, 2009 at 10:26 am
How do I exactly approach? I mean what transformations should I be using to do all the above mentioned steps?
August 24, 2009 at 10:37 am
A couple of different approaches. You could modify your query on the [TableA] to [TableB] load so that duplicates do not exist in the first place. This is generally the preferred approach.
If you prefer to allow the duplicates to come across and then delete, you can use a second data flow to find the duplicates (using a well-written SQL command in the OleDB Connection), output those rows to a file destination and then use the Send Mail Task in the Control Flow to send the duplicates to an admin for review. An Execute SQL Task could be added to the Control Flow to delete the duplicates upon successful sending of the e-mail.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
August 25, 2009 at 3:29 pm
Is this a one-time clean up task? If so, I'd scrap the idea of modeling out in SSIS. Write a query to return the dupes, cut and paste it into Excel, and change the query to a DELETE and run it. Slap a unique constraint onto your column (if you can) and you'll be in the clear.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply