Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
logpop
Starting Member
16 Posts |
 Posted - 2014-01-23 : 13:46:48
|
| if i create ssis packet that synchronizes destination table with source table (destination table must become exact copy of source table) like this:get say 10 new records from source table.compare them with all records from destination table, which could contain millions of records, and do insert if record doesn't exist else do an update.destination table is on remote server, ie those 10 records need to be uploaded.but!do all records from destination table need to be downloaded first for comparison, which would majorly slow task down, or is comparison done on destination server? ssis packet is run on source server. My SSIS packet is using Merge Join. Maybe i need to upload data to temp table and then execute some stored procedure on dest server which will do the update/insert/delete from uploaded tmp table? that would be safest way i think. btw how do i create this tmp table so it will stay alive long enough that stored procedure can work on it?that's my main question.if u want to u can answer my second question (i can figure it out on my own i guess):how do i synchronize two tables? destination table is on remote server.this is my logic:1. upload all records which have changed since last synchronization (I can do this because all my records have lastModifiedDate column).2. compare uploaded records with destination table and do update or insert3. for records which were deleted on source table it gets a little more complicated.i need to do this: upload all keys (but only keys, to work faster) from source table and delete all records from destination table that are not in uploaded keys.or to speed things up i could improve it like this:on source table create "on delete" trigger which writes deleted keys into a new table.when i do synchronization i upload this table and delete all records in destination table which are found in this table. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
logpop
Starting Member
16 Posts |
Posted - 2014-01-24 : 02:40:19
|
| I took the simple approach:I use data flow tasks to simply upload new records and then i use execute sql tasks to do inserts/updates/deletes. i use dynamically created merge statement. nothing else ssis specific, i dont have time for that. seems to be working.for delete i upload all keys from source to dest tmp table. there is no other way. better than to upload all data everytime. i could optimize delete with triggers to save which records got deleted but for now i wont do that. |
 |
|
|
|
|
|
SSIS packet, confusion about what gets uploaded