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;
Tazzmann wrote:
I have been programming for about 13 years and the last few years have been in .NET.