Run DTS Pkg from VBScript (with progress dialog)

'**************************************************************************************************
'* Script Name : Execute DTS Package
'* Type: VBScript
'* Author: Dave Leathem
'*
'* Description : This script will run a DTS package and display the standard package execution
'*  dialog.
'*
'* Notes: 1) This script is designed to work with DTS packages saved as Local Packages.
'*  2) The specified SQL Server must be previously registered in Enterprise Manager.
'*
'**************************************************************************************************



'**************************************************************************************************
'* MAIN Program 
'*
'* The first parameter in the ExecuteDTS call specifies the name of the SQL Server where the DTS
'* package is saved, the second parameter specifies the name of the DTS package.
'**************************************************************************************************

ExecuteDTS "<server name>", "<package name>"

'**************************************************************************************************
'* End MAIN Program
'**************************************************************************************************



'**************************************************************************************************
'* Functions
'**************************************************************************************************

Function ExecuteDTS(ServerName, PkgName)

    Dim oSQLNS
    Dim oSQLNSObj
    Dim eSQLNSRootType
    Dim strApp
    Dim hWnd, hRootItem
    Dim hSrv, eSrv
    Dim hDTS, eDTS
    Dim hLocalDTS, eLocalDTS
    Dim hDTSPkg, eDTSPkg
    Dim eWin, eWinState

    'Create SQL NameSpace object
    Set oSQLNS = CreateObject("SQLNS.SQLNamespace")
    
    'Set constants
    eSQLNSRootType = 0'SQLNSRootType_DefaultRoot
    eSrv = 3'SQLNSOBJECTTYPE_SERVER
    eDTS = 83'SQLNSOBJECTTYPE_DTSPKGS
    eLocalDTS = 84'SQLNSOBJECTTYPE_DTS_LOCALPKGS
    eDTSPkg = 87'SQLNSOBJECTTYPE_DTSPKG
    eWin = 61'SQLNS_CmdID_DTS_RUN
    eWinState = 1'SQLNamespace_PreferModal
    strApp = "SQLNS Application; "
   
    'Initialize (connect) SQL Namespace object
    oSQLNS.Initialize strApp, eSQLNSRootType
    If oSQLNS Is Nothing Then
        MsgBox "SQLNamespace could not be initiated. Terminating.", 0, "Error"
    End If
    
    'Navigate SQL Namespace hierarchy to find DTS package
    hRootItem = oSQLNS.GetRootItem
    hSrv = GetServerHandle(ServerName, oSQLNS, hRootItem)
    hDTS = oSQLNS.GetFirstChildItem(hSrv, eDTS)
    hLocalDTS = oSQLNS.GetFirstChildItem(hDTS, eLocalDTS)
    hDTSPkg = oSQLNS.GetFirstChildItem(hLocalDTS, eDTSPkg, PkgName)

    'Get DTS package object and execute
    Set oSQLNSObj = oSQLNS.GetSQLNamespaceObject(hDTSPkg)
    If MsgBox("Execute Package?", 4, "DTS : " & oSQLNSObj.Name) = 6 Then
        oSQLNSObj.ExecuteCommandByID eWin, hWnd, eWinState
    End If

    'Cleanup
    Set oSQLNS = Nothing
    Set oSQLNSObj = Nothing

End Function

Function GetServerHandle(Server, oSQLNS_sub, hParent)

    Dim hSrvGrp, hSrv
    Dim oSQLNSObj_sub

    'Recursively traverse tree of SQL Server groups to get the SQL Namespace handle
    'for the specified registered SQL Server.
    
    hSrv = 0
    hSrvGrp = oSQLNS_sub.GetFirstChildItem(hParent)
    Do
        If (hSrvGrp = 0) Or (hSrv <> 0) Then
            Exit Do
        Else
            Set oSQLNSObj_sub = oSQLNS_sub.GetSQLNamespaceObject(hSrvGrp)
            If oSQLNSObj_sub.Type = 3 Then
                If oSQLNSObj_sub.Name = Server Then
                    hSrv = oSQLNSObj_sub.Handle
                    Exit Do
                End If
            ElseIf oSQLNSObj_sub.Type = 2 Then
                hSrv = GetServerHandle(Server, oSQLNS_sub, hSrvGrp)
            End If
            hSrvGrp = oSQLNS_sub.GetNextSiblingItem(hSrvGrp)
        End If
    Loop
    
    GetServerHandle = hSrv

End Function

'**************************************************************************************************
'* End Functions
'**************************************************************************************************