Results 1 to 8 of 8

Thread: copying data from sql server to remote mysql DB server

  1. #1
    Join Date
    May 2011
    Posts
    4

    copying data from sql server to remote mysql DB server

    Hi,

    I have a requirement where i have to select some data (data from 7 columns from a table) periodically i.e. every 15 mins from sql server 2005 and insert the data to a remote mysql database server.
    I am thinking of Store-proc to achive this. Can anyone help me with this ?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Couple of options:
    1. create linked server to mysql then copy rows via linked server in sp, set sql job to execute.
    2. build ssis package and run it in sql job.

  3. #3
    Join Date
    May 2011
    Posts
    4
    Can you provide me any link on creating SSIS.
    i don't know how to configure this.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Sql books online has details and Tutorials of ssis, this link has lot of samples: http://www.sqlis.com/.

  5. #5
    Join Date
    May 2011
    Posts
    4
    thanks so much rmiao for the help. Using SSIs i can now connect to remote database and send over the data.
    Also , as this is periodical is it possible that i check the last record sent successfully and then next time the batch runs start from next record.
    Shall i use trigger ??

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Don't need trigger if table has datetime coumn.

  7. #7
    Join Date
    May 2011
    Posts
    4
    Yes , i do have datetime column in my table .
    But can you suggest how can i use that in conjuction with SSIS to monitor what was the last record inserted and then start from there on.
    Do i need to write a sro-proc and write the nusiness logic or is there a way with SSIS that this can be achived ?

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    You can run sql atatement in ssis package. SSIS will not copy data automatically by the way, you have to set sql job to run it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •