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 SourceDetailsAs Admin_upload.EnvironmentDetails,ByVal DestinationDetailsAs Admin_upload.EnvironmentDetails)

'Transfer database from source to destination.Dim oPackageAsNew DTS.Package2Dim oConnectionAs DTS.Connection2Dim oStepAs DTS.Step2Dim oTaskAs DTS.TaskDim oCustomTaskAs DTS.TransferObjectsTask2Try

oStep = oPackage.Steps.New

oTask = oPackage.Tasks.New(

"DTSTransferObjectsTask")

oCustomTask = oTask.CustomTask

oPackage.FailOnError =

FalseWith oStep

.Name =

"Copy Database design and data"

.ExecuteInMainThread =

TrueEndWithWith oTask

.Name =

"GenericPkgTask"EndWithWith 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

EndWith

oStep.TaskName = oCustomTask.Name

oPackage.Steps.Add(oStep)

oPackage.Tasks.Add(oTask)

oPackage.Execute()

Catch exAs Exception

LabelUploadMeta.Text =

"Failed to Upload MetaData: " & ex.MessageThrow exFinally

oConnection =

Nothing

oCustomTask =

Nothing

oTask =

Nothing

oStep =

Nothing

IfNot (oPackageIsNothing)Then

oPackage.UnInitialize()

EndIfEndTryEndSub

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?

Is the second SQL server registered on the SQL server where you run your DTS?

Does you user under DTS is running has rights to access second server?

Thanks