I have two sql server table with lets say employee data. One is in production with yesterday's snapshot and other one is in staging. both tables look alike. Let's say each employee has three addresses. I would like to compare records at the employee level between two tables and only update the employee records where the address was changed, updated or deleted. Here is an example employee table on 04/18/11 - production - columns on top emp# address country 1 virginia usa 1 texas usa 1 new york usa 2 virginia usa 2 texas usa 2 new york usa 3 virginia usa 3 texas usa 3 new york usa here is staging table for 4/19/11 emp# address country 1 virginia usa 1 texas uk 1 alabama uk 2 virginia usa 2 texas usa 2 new york uk 3 virginia usa 3 texas usa 3 new york usa 4 virginia usa 4 texas usa 4 new york usa so I have three employee in prod snapshot table of 4/18/11 and 4 employees in snapshot table of 4/19/11. I basically want to see if any specific employee had any changes to their records. employee 3 has no changes so I don't want to overwrite it. employee 1 and 2 have changes so I want to overwrite them with new version of their records. also I have new employee 4 in 4/19 table so I want to insert that in prod snapshot table. how can I do this? is there some sort of sample that can help me do this in SSIS? or SSIS script task? a programmer on the team told me that I can do this using collections? how do you use collections in sql server or ssis?SQL Pro

How about you look at this this post describing how to Get all from Table A that isn't in Table B http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx ?Arthur My Blog

My scenario is somewhat complicated. I need to able to do this at the employee level. so even if a single field is updated in prod or is different from what I have in staging I have overwrite all the person records in prod with staging and along with generate some historical data as to what records were overwritten. let me know if the scenario isn't clear.SQL Pro

you can use two sources for each table sort each of them with Order By clause, then use Merge Join Transformation and join them based on emp# , then you can use a conditional split transformation and write an expression to compare two address fields fetched out from merge join, if they doesn't match redirect rows to an OLEDB Command to update it. let me know where you need more information?http://www.rad.pasfu.com

well the problem is that my requirement says I need to archive the old records and insert new records if any change occured. and it needs to happen at the employee level and not physical record level.SQL Pro

Can the data be reverified whether the same employee can be at different locations although I agree the fact that it could be possible (like an audit table or so like the locations where employee worked or so). If the employee number is distinct there a simple update and insert query can do the trick : UPDATE B SET B.Country = A.country FROM T1 A JOIN T2 B ON A.EMPNO = B.EMPNO       INSERT INTO T2(EMPNO,COUNTRY) SELECT EMPNO,COUNTRY FROM T1 WHERE EMPNO NOT IN (SELECT EMPNO FROM T2) Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

OK, so you can use Kimball SCD component as described in Todd post completely here: http://toddmcdermid.blogspot.com/2010/01/kimball-method-slowly-changing.htmlhttp://www.rad.pasfu.com

If you can use third-party solutions, check the commercial CozyRoc Table Difference component. You can find new, deleted or updated records with it. The only requirements is to have old and new input sorted with same criteria.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/