动态Tableslist传输SQLSERVER对象

       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