Copy SQL Server Objects Task
With the Copy SQL Server Objects task, you can copy Microsoft® SQL Server™ objects from one instance of SQL Server to another. You can transfer objects such as data and tables, as well as the definitions of objects such as views and stored procedures. Additional objects include referential integrity constraints and indexes.
If you select a table, the Copy SQL Server Objects task will automatically copy any associated table and views that have a foreign key constraint on the selected table. For example, if you transfer the employee table from the pubs database, the jobs and publishers tables also will be transferred. The jobs table will be transferred because of the foreign key relationship between the employee table and the jobs table on the jobs_id field. The publishers table will be transferred because of the foreign key relationship between the employee table and the publishers table on the pub_id field.
If either the destination table or the view exists and contains data, you can specify whether to overwrite or append the incoming data or drop and re-create the table.
You can transfer objects only from:
- One instance of SQL Server version 7.0 to another.
- An instance of SQL Server 7.0 to an instance of SQL Server 2000.
- One instance of SQL Server 2000 to another.
When configuring the Copy SQL Server Objects task, you are not required to create separate source and destination connections. You set source and destination properties in the Copy SQL Server Objects properties dialog box.
Note DTS packages containing Copy SQL Server Objects tasks, where the source and destination are both SQL Server 7.0 databases, may fail if the source server is upgraded to SQL Server 2000 and the package is executed. If the source server was upgraded, you need to refresh each Copy SQL Server Objects task by opening the package in SQL Server 2000, opening each Copy SQL Server Objects task with the above configuration, and clicking the Destination tab and then clicking OK for each of the tasks.
To add a DTS task to a DTS package