TransferObjectsTask Object

The TransferObjectsTask object allows you transfer one or more Microsoft® SQL Server™ objects between source and destination databases. An object can represent:

• A table, or table data.
  
  
• A view.
  
  
• A referential integrity constraint.
  
  
• A stored procedure.
  
  
• An index.
  
  
• A default or a rule.
  
  
• A user-defined data type.
  
  
• In addition, you can transfer all users or all logins (roles) for the source database. You can also transfer all objects dependent on the requested objects.

Note  The source and destination must both be Microsoft SQL Server version 7.0 or later databases.

Collections

Properties Collection

Properties

Methods

Remarks

Certain errors can occur that are documented in an error message written to a log file named server.database.LOG, in the directory specified by the ScriptFileDirectory property. In some cases, these errors may not raise the OnError event, and may not be recorded in the Data Transformation Services (DTS) error file or the SQL Server log.

The TransferObjectsTask object is compatible with SQL Server 7.0. For information about an updated version of this object, see TransferObjectsTask2 Object.

Example

The Microsoft Visual Basic® Sub RunTransfer creates a DTS step and a TransferObjectsTask object. It configures the task to copy the tables authors and employee, the view titleview, and the stored procedure byroyalty, and all objects dependent on these, from the pubs database supplied with SQL Server 2000 to a database named SomeOfPubs.

Private Sub RunTransfer(ByVal objPackage As DTS.Package2)
Dim objStep         As DTS.Step
Dim objTask         As DTS.Task
Dim objXferObj      As DTS.TransferObjectsTask

'create step and task
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSTransferObjectsTask")
Set objXferObj = objTask.CustomTask

'configure transfer objects task
With objXferObj
    .Name = "XferObjTask"
    .SourceServer = "(local)"
    .SourceUseTrustedConnection = True
    .SourceDatabase = "pubs"
    .DestinationServer = "(local)"
    .DestinationUseTrustedConnection = True
    .DestinationDatabase = "SomeOfPubs"
    .ScriptFileDirectory = "D:\DTS_UE\Scripts"
    .CopyAllObjects = False
    .IncludeDependencies = True
    .IncludeLogins = False
    .IncludeUsers = False
    .DropDestinationObjectsFirst = True
    .CopySchema = True
    .CopyData = DTSTransfer_AppendData
    .AddObjectForTransfer "authors", "dbo", DTSSQLObj_UserTable
    .AddObjectForTransfer "employee", "dbo", DTSSQLObj_UserTable
    .AddObjectForTransfer "titleview", "dbo", DTSSQLObj_View
    .AddObjectForTransfer "byroyalty", "dbo", DTSSQLObj_StoredProcedure
End With

'link step to task
objStep.TaskName = objXferObj.Name
objStep.Name = "XferObjStep"
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask
End Sub