SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS packet, confusion about what gets uploaded
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

logpop
Starting Member

16 Posts

Posted - 01/23/2014 :  13:46:48  Show Profile  Reply with Quote
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 insert
3. 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.

Edited by - logpop on 01/23/2014 14:45:26

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/24/2014 :  01:16:08  Show Profile  Reply with Quote
see
http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx

My preferred is get this onto a staging table in destination server, then do the comparison and insert new rows and modify existing ones
or if you're on sql 2008 you can also use MERGE so that even source deleted ones can be taken care of

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

logpop
Starting Member

16 Posts

Posted - 01/24/2014 :  02:40:19  Show Profile  Reply with Quote
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.

Edited by - logpop on 01/24/2014 02:41:15
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000