Create a Windows Form. Then place the controls shown in Figure 7.9, with the following properties set as in Table 7.8.

Table 7.8. Label, ListBox, and Command Button Controls Property Settings

Object	Property	Setting
Label	Name	Label1
	Text	From SQL Servers
ListBox	Name	lstFromSQLServer
Label	Name	Label2
	Text	To SQL Servers
ListBox	Name	lstToSQLServer
Label	Name	Label3
	Text	Transfer from Database
ListBox	Name	lstFromDB
Label	Name	Label4
	Text	Transfer to Database
ListBox	Name	lstToDB
Label	Name	Label5
	Text	Table(s) to Transfer
ListBox	Name	lstTables
	SelectionMode	MultiSimple
Command Button	Name	btnTransfer
	Text	&Perform Transfer

On the form, add the code in Listing 7.18 to the Load event. This will look familiar from How-To 7.1. For an examination of the LoadSQLServers routine, check out step 4 in that How-To. Different from the other How-Tos in this chapter thus far, however, is the fact that the LoadSQLServers routine is called twice: once for the lstFromSQLServer, and a second time for the lstToSQLServer.

Listing 7.18 frmHowTo7_4.vb: Calling the Routine That Loads Available SQL Servers into a List Box

Private Sub frmHowTo7_4_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

        LoadSQLServers(Me.lstFromSQLServer)
        LoadSQLServers(Me.lstToSQLServer)

End Sub

On the lstFromSQLServer and lstToSQLServer list boxes, add the code in Listing 7.19 to the SelectedIndexChanged event of each, as appropriate. These routines call GetSQLDatabases, described in step 6 of How-To 7.1.

Listing 7.19 frmHowTo7_4.vb: Populating the lstDatabases and lstBackupDevices List Boxes

Private Sub lstFromSQLServer_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles lstFromSQLServer.SelectedIndexChanged

        GetSQLDatabases(Me.lstFromSQLServer.SelectedItem, Me.lstFromDB)

    End Sub

Private Sub lstToSQLServer_SelectedIndexChanged(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) _
           Handles lstToSQLServer.SelectedIndexChanged

        GetSQLDatabases(Me.lstToSQLServer.SelectedItem, Me.lstToDB)

End Sub

On the lstFromTables list box, add the code in Listing 7.20 to the SelectedIndexChanged event. This routine starts off by logging onto the server that is selected in the lstFromSQLServer list box, and then creates a reference to the database that is selected in the lstFromDB list box. After clearing the lstTables list box, the routine iterates through each of the tables in the database and adds the names of those that are user tables to the lstTables items.

Listing 7.20 frmHowTo7_4.vb: Populating the lstDatabases and lstBackupDevices List Boxes

Private Sub lstFromDB_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles lstFromDB.SelectedIndexChanged

        '-- Create the connection and specify the stored procedure to use.
        Dim odb As SQLDMO.Database
        Dim otbl As SQLDMO.Table
        Dim oapp As New SQLDMO.Application()
        Dim osvr As New SQLDMO.SQLServer()

        Try

            osvr.LoginSecure = True
            osvr.Connect(Me.lstFromSQLServer.SelectedItem)
            odb = osvr.Databases.Item(Me.lstFromDB.SelectedItem)

            Me.lstTables.Items.Clear()

            For Each otbl In odb.Tables
                If otbl.TypeOf = _
                  SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_UserTable Then
                    Me.lstTables.Items.Add(otbl.Name)
                End If
            Next

        Catch excpData As Exception
            MessageBox.Show("Error Occurred: " & excpData.Message)

        End Try


    End Sub

On the lstTables list box, add the code in Listing 7.21 to the SelectedIndexChanged event. This routine enables the btnTransfer button.

Listing 7.21 frmHowTo7_4.vb: Performing the Transfer of Tables

Private Sub lstTables_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles lstTables.SelectedIndexChanged
        Me.btnTransfer.Enabled = True
End Sub

Add the code in Listing 7.22 to the Click event of btnTransfer. This routine begins by declaring all the objects to be used, and then creates new Step and Task objects, with the type of task being specified. In this case, the task is of type DTSTransferObjectsTask. Next, the various necessary properties are set on the Task object. For each of the tables to be transferred, the AddObjectForTransfer method is executed, with the name of the table being passed to the method. After the name of the task is added to the step, both objects are added to their collections in the Package object. The Execute method of the Package object is then called.

Listing 7.22 frmHowTo7_4.vb: Performing the Transfer of Tables

Private Sub btnTransfer_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles btnTransfer.Click

        Dim oPackage As New DTS.Package()
        Dim oStep As DTS.Step
        Dim oTask As DTS.Task
        Dim oXferObj As DTS.TransferObjectsTask
        Dim intCurrTable As Integer

        Try
            '-- Create step and task

            oStep = oPackage.Steps.New
            oTask = oPackage.Tasks.New("DTSTransferObjectsTask")
            oXferObj = oTask.CustomTask

            '-- Configure transfer objects task

            With oXferObj

                .Name = "XferObjTask"
                .SourceServer = Me.lstFromSQLServer.SelectedItem
                .SourceUseTrustedConnection = True
                .SourceDatabase = Me.lstFromDB.SelectedItem
                .DestinationServer = Me.lstToSQLServer.SelectedItem
                .DestinationUseTrustedConnection = True
                .DestinationDatabase = Me.lstToDB.SelectedItem
                .CopyAllObjects = False
                .IncludeDependencies = True
                .IncludeLogins = False
                .IncludeUsers = False
                .DropDestinationObjectsFirst = False
                .CopySchema = True
                .CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_AppendData

                For intCurrTable = 0 To Me.lstTables.SelectedItems.Count - 1

                    .AddObjectForTransfer( _
                            Me.lstTables.SelectedItems.Item(intCurrTable), "dbo",
                            DTS.DTSSQLObjectType.DTSSQLObj_UserTable)

                Next

            End With

            '-- Link step to task
            oStep.TaskName = oXferObj.Name
            oStep.Name = "XferObjStep"
            oPackage.Steps.Add(oStep)
            oPackage.Tasks.Add(oTask)

            oPackage.Execute()

        Catch excp As Exception

            MessageBox.Show(excp.Message, "Error Occurred")
            Exit Sub

        End Try

        MessageBox.Show("Tables Transferred")

End Sub

Add the code in Listing 7.23 to the Click event of btnClose.

Listing 7.23 frmHowTo7_4.vb: Closing the Form

Private Sub btnClose_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles btnClose.Click

        Me.Close()

End Sub