Search code examples
sqlsql-serversql-server-2008sql-server-2000dts

How do I start the dts transfer program?

I am not that familiar with SQL Server data transfer and I am trying to setup a nightly database transfer for certain tables from SQL Server 2000 to SQL server 2008. I am reading up on DTS, but I dont know how to get it started. Is it possible to have a few steps that would point me in the right direction at least?

EDIT

Issue: How do I copy a Person Table in Personal Database(SQL 2000) to Person Table in Personal Database(SQL 2008) nightly?

Solution

  • You can schedule a DTS by creating SQL Server agent job, and adding the DTS execution as a scheduled task.

    By your use of the term 'DTS' I'm inferring that you are using the SQL Server 2000 tool. Later versions of SQL have SSIS (SQL Server Itegrated Services), which might be a better option for you.

    With this you'll need to

    1. Add a data flow task
    2. Drill down into the data flow task.
    3. Add two database connections, your 2000 and 2008 databases
    4. Add a data source and destination.
    5. Connect the two.

    Give it a shot.