Use this dialog box to specify which objects to transfer from one instance of Microsoft® SQL Server™ to another. You can transfer only between multiple instances of SQL Server version 7.0, from an instance of SQL Server 7.0 to an instance of SQL Server 2000, and between multiple instances of SQL Server 2000.
Create destination objects
Create destination objects for all objects (tables, views, stored procedures, defaults, rules, constraints, user-defined data types, logins, users, roles, and indexes) to be transferred. Enable the following options:
- Drop destination objects first
- Drop all corresponding destination objects before creating new ones.
- Include all dependent objects
- Include all dependent objects, such as the tables supporting a view, in the transfer of data.
- Include all extended properties
- Include all extended properties, which are user-supplied definitions on various objects in the database. For more information, see Using Extended Properties on Database Objects.
Enable the copying of SQL Server data from source to destination and the following copy options:
- Replace existing data
- Overwrite existing data in the destination objects with the new data from the specified source.
- Append data
- Retain existing data in the destination object and append new data from the specified source.
Enable the copying of data between different collations. For more information on using different collations with Data Transformation Services (DTS), see Data Conversion and Transformation Considerations.
Copy all objects
Transfer all objects associated with the specified data source.
Use default options
Set the advanced transfer options to their defaults.
Display the Select Objects dialog box, where you can select individual objects to be transferred. Clear the Transfer all objects check box to make this option available.
Display the Advanced Transfer Options dialog box, where you can further specify the transfer of logins and tables, and whether to use quoted identifiers in the generated SQL Script. Clear the Use default options check box to make this option available.
Script file directory
Specify the directory to which the script file and log files are written.
The SQL and data files for each selected object have script files written to a directory, which must exist on the same computer on which the task runs. These script files are then run against the destination database.
The script and log files remain accessible in the directory until the next time the package is executed. At that time, the files are overwritten with new information. Because the script files are being executed in this manner, running multiple transformations from the same source database at the same time will entail changing the directory where the script file is being stored for one of these transformations. If the script file directory is not changed, one process will not be able to access the file because it is in use.