#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0

    How to compare two tables on two different servers, in SSIS within one package


    Hi,

    I need to compare two tables on two different servers. Could someone help me ?
    Easily I need get rows from two tables (based on same keys), which exists into first table and not exist in second table(based on binary checksum(hashKey)). After that I need to load them into stage table.

    server1.database1.table1.HashKey<>server2.database2.table2.HashKey2 --> this rows I need to received and insert them to table.

    Thank in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    I think the silplest way is to load data from one server to second server into temp table, Do compareson, insert nessesary data, drop temp tables. It would be fastest and easiest way to do that. Alternative way is use linked servers, buteveerything running on linked server really slow...
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by gk53
    I think the silplest way is to load data from one server to second server into temp table, Do compareson, insert nessesary data, drop temp tables. It would be fastest and easiest way to do that. Alternative way is use linked servers, buteveerything running on linked server really slow...
    Yes, but its related to around 6 milion records. And this load would take out a lot of undue time. It must go by another easier way
    What about two connection managers within one data flow ?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by gk53
    I think the silplest way is to load data from one server to second server into temp table, Do compareson, insert nessesary data, drop temp tables. It would be fastest and easiest way to do that. Alternative way is use linked servers, buteveerything running on linked server really slow...
    And what about Loop in loop ? I mean looping through the both servers. and into second of table make sql task with select different data.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    Loop throw 6 milion records takes really long time...
    Dataflow bulk insert will be match faster and after that you just run sql, for sql 6 milion records not really big deal, just do not forget to create clustered index on temp table (only one) and in datasource select in index order

IMN logo majestic logo threadwatch logo seochat tools logo