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=NSW97V9F1S\NSW97V9F1S;Initial Catalog=RGTemp;User Id=rgTest;Password=12345"

'dest.ConnectionString = "SqlServerName=PDNCNLNJ1S\SQLSERVER2005;UseWindowsAuthentication=True;UserName=sa;"

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

source.Name = "Source"

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

'source.ConnectionString = "SqlServerName=NSW97V9F1S\NSW97V9F1S;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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA,'';rgTemp1_log.ldf,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA,''")

'th.Properties("DestinationDatabaseFiles").SetValue(th, "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\rgTemp1.mdf;C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\rgTemp1_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 Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA','';'rgTemp_log.ldf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA',''")

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

What is the error message on validation?

When you add connection manager, the type should be "SMOServer" instead of "OLEDB". So, use something like:
package.Connections.Add("SMOServer")

Setting the properties is easier if you get the InnerObject from task host as in:
TransferDatabaseTask task = (TransferDatabaseTask) th.InnerObject;

For source and destination connections, you should set the name of the connection manager instead of ID. (I am not sure if using the ID is correct)

You have to escape '\' and " in the values for source and destination database file as
"\"rgTemp.mdf\",\"C:\\Program

Files\\Microsoft SQL

Server\\MSSQL.1\\MSSQL\\DATA\",\"\";"\"rgTemp.ldf\",\"C:\\Program

Files\\Microsoft SQL

Server\\MSSQL.1\\MSSQL\\DATA\",\"\";|||

Thanks for the reply

I tried your suggestions and got as follows

Using SMOServer invalidated the connection strings and I could only set them in a format that doesn't allow SQL Server login or a password

I escaped the file names in VB as double double quotes and that fixed the source and destination file name assignments

But the routine still returns 'failed' from the 'validate' call. there is no error message the 'validate' routine just returns 'failed' with no exception or message that I can find.

if i comment out the validate call the 'execute' call just returns failed with no exception.

I am not getting any help from the system here.

we have made a step forward here thanks to you but I am still stuck

Do you have any other ideas?

regards

Ray

|||To create connection manager you can use the following code:
connectionString = String.Format("SqlServerName={0};UseWindowsAuthentication=true;", serverName);
//connectionString = String.Format("SqlServerName={0};UseWindowsAuthentication=false;UserName={1};Password={2}",serverName, userName, passwd);

ConnectionManager connectionManager = package.Connections.Add("SMOServer");
connectionManager.ConnectionString = connectionString;
connectionManager.Name = connectionManagerName;