Option Explicit
Private objPack As New DTS.Package
Private otask As DataPumpTask
Private oSQLTask As Object
Private ostep As DTS.Step
Private Type BrowseInfo
    hWndOwner As Long
    pIDLRoot As Long
    pszDisplayName As Long
    lpszTitle As Long
    ulFlags As Long
    lpfnCallback As Long
    lParam As Long
    iImage As Long
End Type
Private Const BIF_RETURNONLYFSDIRS = 1
Private Const MAX_PATH = 260
Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long

Private Sub cmdLoc_Click()
    Dim iNull As Integer, lpIDList As Long, lResult As Long
    Dim sPath As String, udtBI As BrowseInfo

    With udtBI
        'Set the owner window
        .hWndOwner = Me.hWnd
        'lstrcat appends the two strings and returns the memory address
        .lpszTitle = lstrcat("Choose Data Location", "")
        'Return only if the user selected a directory
        .ulFlags = BIF_RETURNONLYFSDIRS
    End With

    'Show the 'Browse for folder' dialog
    lpIDList = SHBrowseForFolder(udtBI)
    If lpIDList Then
        sPath = String$(MAX_PATH, 0)
        'Get the path from the IDList
        SHGetPathFromIDList lpIDList, sPath
        'free the block of memory
        CoTaskMemFree lpIDList
        iNull = InStr(sPath, vbNullChar)
        If iNull Then
            sPath = Left$(sPath, iNull - 1)
        End If
    End If

    txtFile.Text = sPath
End Sub

Private Sub Command1_Click()
Dim sSQL As String
Dim Succeed As Boolean
Dim i As Integer, errCode As Long, errSource As String, errDesc As String, iErr As Integer
Dim sReplace As String

Screen.MousePointer = vbHourglass
Command1.Enabled = False
'Note that GlobalVariables collection cannot be accessed only by ordinal, not name
objPack.LoadFromSQLServer txtServer, "sa", Trim(txtPwd.Text), , , , , txtPackName.Text
With objPack
    .GlobalVariables(3).Value = txtFile.Text 'FileName (Source Dir)
    .GlobalVariables(2).Value = txtDatabase.Text 'DatabaseName
    .GlobalVariables(1).Value = txtServer.Text 'ServerName
    SetServer txtServer.Text, txtDatabase.Text, txtFile.Text
    If Dir("C:\LogMMImport.txt") <> "" Then Kill "C:\LogMMImport.txt"
    .LogFileName = "C:\LogMMImport.txt"
    For i = 1 To .Tasks.Count
       If InStr(LCase(.Tasks(i).CustomTaskID), "datapump") > 0 Then
            Set otask = .Tasks(i).CustomTask
            If sReplace = "" Then sReplace = Mid(otask.Description, InStr(otask.Description, "[") + 1, InStr(otask.Description, "]") - InStr(otask.Description, "[") - 1)
            otask.SourceObjectName = Mid(otask.Description, InStrRev(otask.Description, "[") + 1, InStrRev(otask.Description, "]") - InStrRev(otask.Description, "[") - 1)
            otask.DestinationObjectName = Replace(otask.DestinationObjectName, sReplace, txtDatabase.Text)
'        ElseIf InStr(LCase(.Tasks(i).CustomTaskID), "dtsexecutesqltask") > 0 Then
'            Set oSQLTask = .Tasks(i).CustomTask
'            If sReplace = "" Then sReplace = Mid(oSQLTask.Description, InStr(oSQLTask.Description, "[") + 1, InStr(oSQLTask.Description, "]") - InStr(oSQLTask.Description, "[") - 1)
'            oSQLTask.Name = Replace(oSQLTask.Name, sReplace, txtDatabase.Text)
'            oSQLTask.Description = Replace(oSQLTask.Description, sReplace, txtDatabase.Text)
'            oSQLTask.SQLStatement = Replace(oSQLTask.SQLStatement, sReplace, txtDatabase.Text)
        End If
    Next
    .Execute
End With

Open "C:\LogMMImport.txt" For Append As #1
Print #1, "Error Messages:"
For Each ostep In objPack.Steps
    If ostep.ExecutionResult = DTSStepExecResult_Failure Then
        ostep.GetExecutionErrorInfo errCode, errSource, errDesc
        Print #1, ostep.Description & " Failed: Code; " & errCode & " Source; " & errSource & " Desc; " & errDesc
        iErr = iErr + 1
    End If
Next
Close #1

If iErr <> 0 Then MsgBox "Errors Occurred:  Check C:\LogMMImport.txt For Details."

objPack.UnInitialize

Set objPack = Nothing
Unload Me
End Sub

Private Sub Command2_Click()
Unload Me
End Sub

' functions used - define before main.
Private Sub SetServer(sServer, sDatabase, sFileName)
    Dim i
    For i = 1 To objPack.Connections.Count
    If objPack.Connections(i).ProviderID = "SQLOLEDB.1" Then
        objPack.Connections(i).DataSource = sServer
        objPack.Connections(i).Catalog = sDatabase
    Else
        objPack.Connections(i).DataSource = sFileName
    End If
    Next
End Sub