April 19, 2011 at 10:05 am
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?
Thoughts???
April 19, 2011 at 10:14 am
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
April 19, 2011 at 10:20 am
Since these changes are based on Insert/update/delete have you thought about doing this with triggers rather than and SSIS. doing this with triggers would mean the update would happen as soon as the change was made rather than some scheduled time when the SSIS was run. If you do need to do this with SSIS and if you are simply trying to bring the snapshot DB into synch why not truncate and reload the table? I am also assuming that while you say snapshot that this is not actualy a snapshot replication.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 19, 2011 at 10:21 am
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.
I cant overwrite or truncate the prod table. I am only allowed to archive employee and insert new snapshot from staging only for that employee.
April 19, 2011 at 10:28 am
I still think a trigger might be the better way to go. When an update is made in staging you could handle that anyway you need to. weather that be write that single piece of information back to prod or update prod and write the change to an audit DB for tracking the changes. A trigger would seem to make that a little more stream lined.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 19, 2011 at 10:58 am
Can you join and compare?
SELECT
emp#
FROM Production P
INNER JOIN Staging S
ON P.emp# = S.emp#
WHERE P.Address <> S.Address OR P.country<> S.country
Once you have the identified the changed employess you can do your archiving/deleting/inserting.
I would build a stored procedure to do this and call it from the package. Do you need a pure SSIS solution?
April 19, 2011 at 11:23 am
Most important thing here is that it needs to happen NOT at the record but employee level. I am not sure a trigger can handle this.
April 19, 2011 at 11:45 am
I am not sure I understand the distinction between the employee and the record level.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 19, 2011 at 2:06 pm
It looks like a single employee can have multiple records.
1 virginia usa
1 texas usa
1 new york usa
Can an employee have more than one record in the same state?
If you can provide full table CREATE TABLE statements including and keys/indexes we may be able to offer additional help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply