Transfer SQL Server Objects Task
The Transfer SQL Server Objects task transfers one or more types of objects in a SQL Server database between instances of SQL Server. For example, the task can copy tables and stored procedures. Depending on the version of SQL Server that is used as a source, different types of objects are available to copy. For example, only a SQL Server database includes schemas and user-defined aggregates.
The Transfer SQL Server Objects task can be configured to transfer all objects, all objects of a type, or only specified objects of a type. For example, you can choose to copy only selected tables in the AdventureWorks database.
If the Transfer SQL Server Objects task transfers tables, you can specify the types of table-related objects to copy with the tables. For example, you can specify that primary keys are copied with tables.
Server roles, roles, and users from the specified database can be copied, as well as the permissions for the transferred objects. By copying the associated users, roles, and permissions together with the objects, you can make the transferred objects immediately operable on the destination server.
To further enhance functionality of transferred objects, you can configure the Transfer SQL Server Objects task to include schema names, data, extended properties of transferred objects, and dependent objects in the transfer. When copying data, you can specify whether to replace or append existing data.
At run time, the Transfer SQL Server Objects task connects to the source and destination servers by using two SMO connection managers. The SMO connection managers are configured separately from the Transfer SQL Server Objects task, and then referenced in the Transfer SQL Server Objects task. The SMO connection managers specify the server and the authentication mode to use when accessing the server. For more information, see SMO Connection Manager.
Objects to Transfer
The following table lists the type of objects that can be copied. The version column identifies the SQL Server version that the task can use as a source when including the object type in a transfer.
Object |
Version |
|---|---|
Tables |
SQL Server 2000 or SQL Server |
Views |
SQL Server 2000 or SQL Server |
Stored Procedures |
SQL Server 2000 or SQL Server |
User-Defined Functions |
SQL Server 2000 or SQL Server |
Defaults |
SQL Server 2000 or SQL Server |
User-Defined Data Types |
SQL Server 2000 or SQL Server |
Partition Functions |
SQL Server only |
Partition Schemes |
SQL Server only |
Schemas |
SQL Server only |
Assemblies |
SQL Server only |
User-Defined Aggregates |
SQL Server only |
User-Defined Types |
SQL Server only |
XML Schema Collection |
SQL Server only |
User-defined data types that were created in an instance of SQL Server 2000 are based on the system data types in SQL Server 2000. When transferred to SQL Server, these user-defined data types are created using the system data types of SQL Server and saved as aliased data types. For more information about data types in SQL Server, see Data Types (Database Engine).
User-defined types (UDTs) that were created in an instance of SQL Server have dependencies on common language runtime (CLR) assemblies. If you use the Transfer SQL Server Objects task to transfer UDTs, you must also configure the task to transfer dependent objects. To transfer dependent objects, set the IncludeDependentObjects property to True.
Table Options
When copying tables, you can indicate the types of table-related items to include in the copy process. The following types of items can be copied together with the related table:
Indexes
Triggers
Full-text indexes
Primary keys
Foreign keys
You can also indicate whether the script that the task generates is in Unicode format.
Destination Options
You can configure the Transfer SQL Server Objects task to include schema names, data, extended properties of transferred objects, and dependent objects in the transfer. If data is copied, it can replace or append existing data.
Some options apply only to SQL Server. For example, only SQL Server supports schemas.
Security Options
The Transfer SQL Server Objects task can include SQL Server database-level users and roles from the source, SQL Server logins, and the permissions for transferred objects. For example, the transfer can include the permissions on the transferred tables.
Transferring Objects Between Instances of SQL Server
The Transfer SQL Server Objects task supports a source and destination that is SQL Server 2000 or SQL Server. There are no restrictions on which version to use as a source or destination.
Events
The task raises an information event that reports the object transferred and a warning event when an object is overwritten. An information event is also raised for actions such as the truncation of database tables.
The Transfer SQL Server Objects task does not report incremental progress of the object transfer; it reports only 0% and 100 % completion.
Execution Value
The execution value, stored in the ExecutionValue property of the task, returns the number of objects transferred. By assigning a user-defined variable to the ExecValueVariable property of the Transfer SQL Server Objects task, information about the object transfer can be made available to other objects in the package. For more information, see Integration Services Variables and Using Variables in Packages.
Log Entries
The Transfer SQL Server Objects task includes the following custom log entries:
TransferSqlServerObjectsTaskStartTransferringObjects This log entry reports that the transfer has started. The log entry includes the start time.
TransferSqlServerObjectsTaskFinishedTransferringObjects This log entry reports that the transfer has completed. The log entry includes the end time.
In addition, a log entry for an OnInformation event reports the number of objects of the object types that have been selected for transfer, the number of objects that were transferred, and actions such as the truncation of tables when data is transferred with tables. A log entry for the OnWarning event is written for each object on the destination that is overwritten.
Security and Permissions
The user must have permission to browse objects on the source server, and must have permission to drop and create objects on the destination server; moreover, the user must have access to the specified database and database objects.
Configuring the Transfer SQL Server Objects Task
You can set properties through SSIS Designer or programmatically.
For more information about the properties that you can set in SSIS Designer, click one of the following topics:
For more information about how to set these properties in SSIS Designer, click the following topic:
Configuring the Transfer SQL Server Objects Task Programmatically
For more information about programmatically setting these properties, click the following topic:
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services