Programatic Transferdatabasetask In Ssis

I am trying to code a package that runs a transferdatabasetask with the following code

Dim package As New Package()

package.PackageType = DTSPackageType.DTSDesigner90

package.Name = "transfer db task"

package.Description = "transfer db task"

package.CreatorComputerName = System.Environment.MachineName

package.CreatorName = System.Environment.UserName

Dim dest As ConnectionManager = package.Connections.Add("OLEDB")

dest.Name = "Dest"

dest.ConnectionString = "Data Source=NSW97V9F1SNSW97V9F1S;Initial Catalog=RGTemp;User Id=rgTest;Password=12345"

'dest.ConnectionString = "SqlServerName=PDNCNLNJ1SSQLSERVER2005;UseWindowsAuthentication=True;UserName=sa;"

Dim source As ConnectionManager = package.Connections.Add("OLEDB")

source.Name = "Source"

source.ConnectionString = "Data Source=NSW97V9F1SNSW97V9F1S;Initial Catalog=RGTemp;User Id=rgTest;Password=12345"

'source.ConnectionString = "SqlServerName=NSW97V9F1SNSW97V9F1S;UseWindowsAuthentication=True;UserName=;"

Dim th As TaskHost = TryCast(package.Executables.Add("STOCK:TransferDatabaseTask"), TaskHost)

th.Name = "transfer db task"

th.Description = "The transfer task"

th.Properties("Action").SetValue(th, 0) '0: copy

th.Properties("Method").SetValue(th, 1) '1: destination online

th.Properties("DestinationConnection").SetValue(th, dest.ID)

th.Properties("DestinationDatabaseFiles").SetValue(th, "rgTemp1.mdf,C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA,'';rgTemp1_log.ldf,C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA,''")

'th.Properties("DestinationDatabaseFiles").SetValue(th, "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA
gTemp1.mdf;C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA
gTemp1_log.ldf")

th.Properties("DestinationDatabaseName").SetValue(th, "RGTemp1")

th.Properties("DestinationOverwrite").SetValue(th, True)

th.Properties("ReattachSourceDatabase").SetValue(th, False)

th.Properties("SourceConnection").SetValue(th, source.ID)

th.Properties("SourceDatabaseFiles").SetValue(th, "'rgTemp.mdf','C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA','';'rgTemp_log.ldf','C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA',''")

MsgBox(th.Properties("SourceDatabaseFiles").GetValue(th).ToString)

MsgBox(th.Properties("DestinationDatabaseFiles").GetValue(th).ToString)

th.Properties("SourceDatabaseName").SetValue(th, "RGTemp")

Dim status As DTSExecResult = package.Validate(Nothing, Nothing, Nothing, Nothing)

' If the package validated successfully, then execute it.

If status = DTSExecResult.Success Then

' Execute the package

Dim result As DTSExecResult = package.Execute(Nothing, Nothing, Nothing, Nothing, Nothing)

End If

'Dts.TaskResult = Dts.Results.Success

End Sub



The problem is that the package validation fails and the 'execute' statement never runs. the two message boxes report zero length strings in the source and destination files properties. It seems that the hard coded filenames that I have provided are not correct.

Can anyone shed any light on what is wrong here:

regards

Ray