Script Task组件:
Dim pkg As New Package()
Dim MoveTable As Executable = pkg.Executables.Add("STOCK:TransferSqlServerObjectsTask")
Dim MoveTableTask As TaskHost = CType(MoveTable, TaskHost)
Dim Datasource As ConnectionManager = pkg.Connections.Add("SMOServer")
Datasource.ConnectionString = "SqlServerName=InstanceName1;UseWindowsAuthentication=False;UserName=user;"
Datasource.Properties("Password").SetValue(Datasource, "PPP")
Datasource.Name = "TestConn_source"
Dim Destination As ConnectionManager = pkg.Connections.Add("SMOServer")
Destination.ConnectionString = "SqlServerName=Instancename2;UseWindowsAuthentication=False;UserName=user;"
Destination.Properties("Password").SetValue(Destination, "PPP")
Destination.Name = "TestConn_Destination"
MoveTableTask.Properties("CopyData").SetValue(MoveTableTask, True)
MoveTableTask.Properties("CopySchema").SetValue(MoveTableTask, True)
MoveTableTask.Properties("CopyIndexes").SetValue(MoveTableTask, True)
MoveTableTask.Properties("DropObjectsFirst").SetValue(MoveTableTask, True)
MoveTableTask.Properties("ExistingData").SetValue(MoveTableTask, Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.ExistingData.Replace)
MoveTableTask.Properties("CopyAllTables").SetValue(MoveTableTask, False)
' Read the records from Object variable and put them into StringCollection
Dim Tables As Collections.Specialized.StringCollection = New Collections.Specialized.StringCollection()
Dim oleDA As New OleDb.OleDbDataAdapter
Dim dt As New DataTable
Dim row As DataRow
oleDA.Fill(dt, Dts.Variables("Tablename").Value)
For Each row In dt.Rows
Tables.Add(row("name").ToString())
Next
'MsgBox(Tables.Count)
MoveTableTask.Properties("TablesList").SetValue(MoveTableTask, Tables)
MoveTableTask.Properties("SourceConnection").SetValue(MoveTableTask, Datasource.Name)
MoveTableTask.Properties("SourceDatabase").SetValue(MoveTableTask, "Db1")
MoveTableTask.Properties("DestinationConnection").SetValue(MoveTableTask, Destination.Name)
MoveTableTask.Properties("DestinationDatabase").SetValue(MoveTableTask, "Db2")
' Execute package and dispose
pkg.Execute()
注意事项:
1、add reference : system:XML
2、add :Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask