Post reply

Scheduling DTS to move data

  • September 18, 2003 at 10:50 am

    #159882

    Hi everyone again!

    I am not sure if this question is best suited for this forum or for the Administration forum.

    Please tell me if this is not the right forum in case of next time.

    I have got some data that I need to move from access db to sql server db.

    I need to set up a DTS schedule to run this job once every week.

    In the past, what I have done is use DTS to move data once and don't have have to worry about it anymore.

    This time though, I am required to schedule this to run once every week.

    This means that the Access db that we will be moving data from, is not going away.

    I know that the first time DTS schedule runs, it will move entire data from Access DB to SQL Server DB.

    My question is, after that initial data movement, once a DTS is scheduled to run once a week, will it just pick up new data from access db and append it to existing data in sql server every week or will copy the entire data from access db and overwrite existing data in sql server.

    Our goal is to not move entire data every week but just copy new ones and append it to the ones already existing in sql server.

    If DTS can't do this, is there any scripts/links where I can find a script that will help me accomplish this?

    As usual, thanks in advance for the assistance.

  • September 18, 2003 at 6:25 pm

    #474967

    Yes you can do this with DTS. However, it won't be as easy asjust setting up the DTS package in the wizard.

    Can you set up the Access database as a linked server? If so, then you can setup a series of stored procedures to do updates and inserts into the SQL Server tables.

    If you can't setup a linked server, then I'd create copies of the Access tables in the SQL Server database. Use DTS to copy all the data to these tables. Then use the same stored procedure approach to update and insert data as required.

    In both instances, you can use DTS to handle the workflow to ensure things get updated and inserted in the correct order.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    Edited by - phillcart on 09/18/2003 6:27:13 PM

    --------------------
    Colt 45 - the original point and click interface

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply