Transfer Tasks SMO - works Intermittently

Category: sql server smodmo

Question

KEHS on Thu, 07 Mar 2019 14:35:21


Hi 

I have dynamically created a transfer tasks object to transfer tables from source to destination. However this is working intermittently. For example I set stringcollection() with TableA, TableB and TableC as tables to transfer. first time round the tables transfer as expected. I then drop TableB and TableC in destination and re-execute the package. However this time round none of the tables transfer. Note property DropObjectsFirst is set tot True.

Can anybody help ?

  Public Sub Main()
        '
        Dim SourceConnection As String
        SourceConnection = Dts.Variables("User::SourceConnection").Value.ToString
        Dim SourceDatabase As String
        SourceDatabase = Dts.Variables("$Package::SourceDb").Value.ToString


        Dim DestinationConnection As String
        DestinationConnection = Dts.Variables("User::DestinationConnection").Value.ToString
        Dim DestinationDatabase As String
        DestinationDatabase = Dts.Variables("$Package::TargetDb").Value.ToString

        Dim pkg As Package = New Package()
        Dim conns As Connections = pkg.Connections


        ' Get variable values
        Dts.Events.FireInformation(0, "", ">User::SourceConnection is set to: " + Dts.Variables("User::SourceConnection").Value.ToString(), Nothing, -1, True)
        Dts.Events.FireInformation(0, "", ">$Package::SourceDb is set to: " + Dts.Variables("$Package::SourceDb").Value.ToString(), Nothing, -1, True)
        Dts.Events.FireInformation(0, "", ">$Package::SourceSrv is set to: " + Dts.Variables("$Package::SourceSrv").Value.ToString(), Nothing, -1, True)

        Dts.Events.FireInformation(0, "", ">User::DestinationConnection is set to: " + Dts.Variables("User::DestinationConnection").Value.ToString(), Nothing, -1, True)
        Dts.Events.FireInformation(0, "", ">$Package::TargetDb is set to: " + Dts.Variables("$Package::TargetDb").Value.ToString(), Nothing, -1, True)
        Dts.Events.FireInformation(0, "", ">$Package::TargetSrv is set to: " + Dts.Variables("$Package::TargetSrv").Value.ToString(), Nothing, -1, True)


        'create a stringcollection of tables

        Dim Tables As StringCollection = New StringCollection()     'Holds String values
        Dim DataTable As New DataTable
        Dim TableRow As DataRow
        Dim adapter As New OleDb.OleDbDataAdapter                   'Holds a DataAdapter OBJECT

        'Manually populate stringcollection() -- used for testing
        'Tables.Add("[LocalFeeds].[tbUHL_WEEKLY_EXTRACTSUHL_IMAGING_DA]")
        'Tables.Add("[LocalFeeds].[tbUHL_WEEKLY_EXTRACTSUHL_IMAGING_PET]")
        'Tables.Add("[LocalFeeds].[tbUHL_WEEKLY_EXTRACTSUHL_IMAGING_WAITING_LIST]")

        adapter.Fill(DataTable, Dts.Variables("User::TableName").Value)
        For Each TableRow In DataTable.Rows

            Tables.Add(TableRow(0))  ' Add tablenames to string collection which is subsequently passed in as <tablelist>. 
        Next


        'Add a ConnectionManager to the Connections collection.
        Dim cmSource As ConnectionManager = pkg.Connections.Add("SMOServer")
        cmSource.Name = "SMOSourceServer"
        cmSource.ConnectionString = SourceConnection

        Dim cmDestination As ConnectionManager = pkg.Connections.Add("SMOServer")
        cmDestination.Name = "SMODestinationServer"
        cmDestination.ConnectionString = DestinationConnection

        'create SQL Server object task to move tables
        Dim xfr As Executable = pkg.Executables.Add("STOCK:TransferSqlServerObjectsTask")
        Dim xfrTask As TaskHost = CType(xfr, TaskHost)

        ' Setting properties for TransferSqlServerObjectsTask control
        xfrTask.Properties("SourceConnection").SetValue(xfrTask, cmSource.Name)
        xfrTask.Properties("SourceDatabase").SetValue(xfrTask, SourceDatabase)
        xfrTask.Properties("DestinationConnection").SetValue(xfrTask, cmDestination.Name)
        xfrTask.Properties("DestinationDatabase").SetValue(xfrTask, DestinationDatabase)
        xfrTask.Properties("CopyAllObjects").SetValue(xfrTask, False)
        xfrTask.Properties("CopyAllTables").SetValue(xfrTask, False)
        xfrTask.Properties("CopyAllSchemas").SetValue(xfrTask, True)
        xfrTask.Properties("UseCollation").SetValue(xfrTask, True)

        xfrTask.Properties("DropObjectsFirst").SetValue(xfrTask, True)
        xfrTask.Properties("CopyData").SetValue(xfrTask, True)
        xfrTask.Properties("CopySchema").SetValue(xfrTask, True)
        xfrTask.Properties("ExistingData").SetValue(xfrTask, 1)


        xfrTask.Properties("TablesList").SetValue(xfrTask, Tables)



        xfrTask.Properties("CopyPrimaryKeys").SetValue(xfrTask, False)
        xfrTask.Properties("CopyIndexes").SetValue(xfrTask, False)
        xfrTask.Properties("CopyTriggers").SetValue(xfrTask, False)


        pkg.Execute()



        Dts.TaskResult = ScriptResults.Success
    End Sub