locked
Moving specific data from one db to another on different server and instance RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have an archive db and want to move only recently updated data from it to a slimmed down copy on another server and instance. In the table(s) there is an "archived on" date value so I only want to move data that is greater than the value in the receiving table (i.e anything that has been moved into the original archive db since the last time this process ran). Figure it's best to look at the max date value in the receiving table and transfer anything that is greater than that value. Need to do it this way as one of the tables does not have a key so potential for duplicates if the process were, god forbid, run twice is there. Is there a way to do this without having to set up linked servers?

    something like this

    select * from server1\instance1\db1\table where [archive_dt  > (select max(archive_dt) from server2\instance2\db2\table)]

    Thanks for the help as I am relatively new to this and looking for the most low impact way to get this done.

    Monday, June 10, 2013 6:59 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote 
     

    Is there a way to do this without having to set up linked servers?

    As Arthur mentioned, there is no need of linked servers in SSIS. BTW, linked server's degrade the performance and impacts resources.

    I am relatively new to this and looking for the most low impact way to get this done.<input id="c31fe3f7-0519-4b23-b56b-6a6961c80410_attachments" type="hidden" />

    Here are the steps for you:

    1. Drag a exec sql task and paste this query 
    select max(archive_dt) from server2\instance2\db2\table

    and save the result set into a variable. This value let you know from where the next time SSIS would fetch data from source table.

                   2.  Then, create a package scoped variable of data type string. Go to the expressions page and hit evaluate expression as true. Then hit the ellipses button and paste this query. 

    "
select * from server1\instance1\db1\table where archive_dt > "+[User::Variable]+"
"

    3. drag a ole db source. Go to data flow, drag ole db source, data access mode as variable, select the variable that has SQL query.

    4. Drag ole db destionation and perform mappings

    5. test your package output.               

    Thanks, hsbal

    • Proposed as answer by Mike Yin Wednesday, June 12, 2013 11:26 AM
    • Marked as answer by Mike Yin Monday, June 17, 2013 6:27 AM
    Monday, June 10, 2013 8:05 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    No need for linked server, after all SSIS uses a one time so called ad-hock linkage by T-SQL openrowset just drag and drop the OLEDB Data Source and use the above query w/o the server name and db in it. Then drag and drop an OLEDB Destination that will be another server.

    Arthur My Blog

    Monday, June 10, 2013 7:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote 
     

    Is there a way to do this without having to set up linked servers?

    As Arthur mentioned, there is no need of linked servers in SSIS. BTW, linked server's degrade the performance and impacts resources.

    I am relatively new to this and looking for the most low impact way to get this done.<input id="c31fe3f7-0519-4b23-b56b-6a6961c80410_attachments" type="hidden" />

    Here are the steps for you:

    1. Drag a exec sql task and paste this query 
    select max(archive_dt) from server2\instance2\db2\table

    and save the result set into a variable. This value let you know from where the next time SSIS would fetch data from source table.

                   2.  Then, create a package scoped variable of data type string. Go to the expressions page and hit evaluate expression as true. Then hit the ellipses button and paste this query. 

    "
select * from server1\instance1\db1\table where archive_dt > "+[User::Variable]+"
"

    3. drag a ole db source. Go to data flow, drag ole db source, data access mode as variable, select the variable that has SQL query.

    4. Drag ole db destionation and perform mappings

    5. test your package output.               

    Thanks, hsbal

    • Proposed as answer by Mike Yin Wednesday, June 12, 2013 11:26 AM
    • Marked as answer by Mike Yin Monday, June 17, 2013 6:27 AM
    Monday, June 10, 2013 8:05 PM