Dedup logic in SSIS
Hi, I have task to trasnfer the records from Oracle to SQL on daily basis. Once in a day. Trasnfer the records while no one is touching the records. I am tranferring using SSIS. Everyday when i trasnfer, I want to make sure that I do not trasnfer previous day records. I have read only permission to oracle tables. Is there any transformation in SSIS package which match up the recorsds between 2 environments and exclude duplicates or dnt trasnfer previus day records? thanks, shalin
July 17th, 2012 4:34pm

How many records are we talking about? You could merge join the two sources on their (business) key.... or use a Lookup for that. And if you realy want, you could use the SCD transformation for it (slow). And if you want to compare all columns you could create a hash of both records and compare those two hashes. Here are some hash components http://microsoft-ssis.blogspot.com/2012/04/custom-ssis-component-checksum.html http://www.sqlis.com/post/Checksum-Transformation.aspx Alternative could be a unique constraint on your destination database.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
Free Windows Admin Tool Kit Click here and download it now
July 17th, 2012 5:39pm

If the records are simple such as numbers you can use look up transform or if your business rules for deduplication are complex and than you have to look for other transforms such as fuzzygroup and fuzzy logic Thanks
July 17th, 2012 7:14pm

Does the table not have any date column like createDate or updateDate to show when the records were modified. If they are why not use them and keep things simple?My Blog | Ask Me | SSIS Basics
Free Windows Admin Tool Kit Click here and download it now
July 18th, 2012 1:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics