DTS Transfer

Hi,

 I have the following method which transfers data SQL to SQL on the same server but when I try to change the destination server it won't transfer the data. The method runs through as expected with no exceptions or errors but with no data transfered.

 

Private Sub TransferSQLData(ByVal SourceDetails As Admin_upload.EnvironmentDetails, ByVal DestinationDetails As Admin_upload.EnvironmentDetails)

 

'Transfer database from source to destination.

Dim oPackage As New DTS.Package2

Dim oConnection As DTS.Connection2

Dim oStep As DTS.Step2

Dim oTask As DTS.Task

Dim oCustomTask As DTS.TransferObjectsTask2

Try

oStep = oPackage.Steps.New

oTask = oPackage.Tasks.New("DTSTransferObjectsTask")

oCustomTask = oTask.CustomTask

oPackage.FailOnError = False

With oStep

.Name = "Copy Database design and data"

.ExecuteInMainThread = True

End With

With oTask

.Name = "GenericPkgTask"

End With

With oCustomTask

.Name = "DTSTransferObjectsTask"

.SourceServer = "MYSERVER"

.SourceUseTrustedConnection = True

.SourceDatabase = SourceDetails.MetaDB

.SourceLogin = SourceDetails.MetaUser

.SourcePassword = SourceDetails.MetaPWD

.DestinationServer = "MYSERVER"

.DestinationUseTrustedConnection = True

.DestinationDatabase = DestinationDetails.MetaDB

.DestinationLogin = DestinationDetails.MetaUser

.DestinationPassword = DestinationDetails.MetaPWD

.CopyAllObjects = True

.IncludeDependencies = False

.IncludeLogins = False

.IncludeUsers = False

.DropDestinationObjectsFirst = True

.CopySchema = True

.CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_ReplaceData

 

End With

oStep.TaskName = oCustomTask.Name

oPackage.Steps.Add(oStep)

oPackage.Tasks.Add(oTask)

oPackage.Execute()

Catch ex As Exception

LabelUploadMeta.Text = "Failed to Upload MetaData: " & ex.Message

Throw ex

Finally

oConnection = Nothing

oCustomTask = Nothing

oTask = Nothing

oStep = Nothing

 

If Not (oPackage Is Nothing) Then

oPackage.UnInitialize()

End If

End Try

End Sub

This works fine but when I set the following within the method:

.DestinationServer = "ANOTHERSERVER"

It won't transfer the data.

I can access the remote server and read and write data to it.

 Any ideas?