How can I create a human-readable script for every DTS package on a SQL server?

Question

I know I can edit each individual DTS package and save it as a Visual Basic script, but with hundreds of packages on the server, that will take forever. How can I script them all at once? I'd like to be able to create one file per package so that I can check them into source control, search them to see which one references a specific table, or compare the packages on our development server to the packages on our production server.

Answer 1

I ended up digging through the SQL 2000 documentation (Building SQL Server Applications / DTS Programming / Programming DTS Applications / DTS Object Model) and creating a VBS script to read the packages and write XML files. It's not complete, and it could be improved in several ways, but it's a big start:

GetPackages.vbs

Option Explicit

Sub GetProperties (strPackageName, dtsProperties, xmlDocument, xmlProperties)
    Dim dtsProperty

    If Not dtsProperties Is Nothing Then
        For Each dtsProperty in dtsProperties
            If dtsProperty.Set Then
                Dim xmlProperty
                Set xmlProperty = xmlProperties.insertBefore ( _
                    xmlDocument.createElement ("Property"), _
                    xmlProperties.selectSingleNode ("Property[@Name > '" & dtsProperty.Name & "']"))

                'properties
                'xmlProperty.setAttribute "Get",    dtsProperty.Get
                'xmlProperty.setAttribute "Set",    dtsProperty.Set
                xmlProperty.setAttribute "Type",    dtsProperty.Type
                xmlProperty.setAttribute "Name",    dtsProperty.Name

                If not isnull(dtsProperty.Value) Then
                    xmlProperty.setAttribute "Value",   dtsProperty.Value
                End If

                'collections
                'getting properties of properties causes a stack overflow
                'GetProperties strPackageName, dtsProperty.Properties, xmlDocument, xmlProperty.appendChild (xmlDocument.createElement ("Properties"))
            End If
        Next
    End If
End Sub

Sub GetOLEDBProperties (strPackageName, dtsOLEDBProperties, xmlDocument, xmlOLEDBProperties)
    Dim dtsOLEDBProperty

    For Each dtsOLEDBProperty in dtsOLEDBProperties
        If dtsOLEDBProperty.IsDefaultValue = 0 Then
            Dim xmlOLEDBProperty
            Set xmlOLEDBProperty = xmlOLEDBProperties.insertBefore ( _
                xmlDocument.createElement ("OLEDBProperty"), _
                xmlOLEDBProperties.selectSingleNode ("OLEDBProperty[@Name > '" & dtsOLEDBProperty.Name & "']"))

            'properties
            xmlOLEDBProperty.setAttribute "Name",           dtsOLEDBProperty.Name
            'xmlOLEDBProperty.setAttribute "PropertyID",        dtsOLEDBProperty.PropertyID
            'xmlOLEDBProperty.setAttribute "PropertySet",       dtsOLEDBProperty.PropertySet
            xmlOLEDBProperty.setAttribute "Value",          dtsOLEDBProperty.Value
            'xmlOLEDBProperty.setAttribute "IsDefaultValue",    dtsOLEDBProperty.IsDefaultValue

            'collections
            'these properties are the same as the ones directly above
            'GetProperties strPackageName, dtsOLEDBProperty.Properties, xmlDocument, xmlOLEDBProperty.appendChild (xmlDocument.createElement ("Properties"))
        End If
    Next
End Sub

Sub GetConnections (strPackageName, dtsConnections, xmlDocument, xmlConnections)
    Dim dtsConnection2

    For Each dtsConnection2 in dtsConnections
        Dim xmlConnection2
        Set xmlConnection2 = xmlConnections.insertBefore ( _
            xmlDocument.createElement ("Connection2"), _
            xmlConnections.selectSingleNode ("Connection2[@Name > '" & dtsConnection2.Name & "']"))

        'properties
        xmlConnection2.setAttribute "ID",       dtsConnection2.ID
        xmlConnection2.setAttribute "Name",     dtsConnection2.Name
        xmlConnection2.setAttribute "ProviderID",   dtsConnection2.ProviderID

        'collections
        GetProperties strPackageName, dtsConnection2.Properties, xmlDocument, xmlConnection2.appendChild (xmlDocument.createElement ("Properties"))

        Dim dtsOLEDBProperties
        On Error Resume Next
        Set dtsOLEDBProperties = dtsConnection2.ConnectionProperties

        If Err.Number = 0 Then
            On Error Goto 0
            GetOLEDBProperties strPackageName, dtsOLEDBProperties, xmlDocument, xmlConnection2.appendChild (xmlDocument.createElement ("ConnectionProperties"))
        Else
            MsgBox Err.Description & vbCrLf & "ProviderID: " & dtsConnection2.ProviderID & vbCrLf & "Connection Name: " & dtsConnection2.Name, , strPackageName
            On Error Goto 0
        End If

    Next
End Sub

Sub GetGlobalVariables (strPackageName, dtsGlobalVariables, xmlDocument, xmlGlobalVariables)
    Dim dtsGlobalVariable2

    For Each dtsGlobalVariable2 in dtsGlobalVariables
        Dim xmlGlobalVariable2
        Set xmlGlobalVariable2 = xmlGlobalVariables.insertBefore ( _
            xmlDocument.createElement ("GlobalVariable2"), _
            xmlGlobalVariables.selectSingleNode ("GlobalVariable2[@Name > '" & dtsGlobalVariable2.Name & "']"))

        'properties
        xmlGlobalVariable2.setAttribute "Name",     dtsGlobalVariable2.Name

        If Not Isnull(dtsGlobalVariable2.Value) Then
            xmlGlobalVariable2.setAttribute "Value",    dtsGlobalVariable2.Value
        End If

        'no extended properties

        'collections
        'GetProperties strPackageName, dtsGlobalVariable2.Properties, xmlDocument, xmlGlobalVariable2.appendChild (xmlDocument.createElement ("Properties"))
    Next
End Sub

Sub GetSavedPackageInfos (strPackageName, dtsSavedPackageInfos, xmlDocument, xmlSavedPackageInfos)
    Dim dtsSavedPackageInfo

    For Each dtsSavedPackageInfo in dtsSavedPackageInfos
        Dim xmlSavedPackageInfo
        Set xmlSavedPackageInfo = xmlSavedPackageInfos.appendChild (xmlDocument.createElement ("SavedPackageInfo"))

        'properties
        xmlSavedPackageInfo.setAttribute "Description",     dtsSavedPackageInfo.Description
        xmlSavedPackageInfo.setAttribute "IsVersionEncrypted",  dtsSavedPackageInfo.IsVersionEncrypted
        xmlSavedPackageInfo.setAttribute "PackageCreationDate", dtsSavedPackageInfo.PackageCreationDate
        xmlSavedPackageInfo.setAttribute "PackageID",       dtsSavedPackageInfo.PackageID
        xmlSavedPackageInfo.setAttribute "PackageName",     dtsSavedPackageInfo.PackageName
        xmlSavedPackageInfo.setAttribute "VersionID",       dtsSavedPackageInfo.VersionID
        xmlSavedPackageInfo.setAttribute "VersionSaveDate", dtsSavedPackageInfo.VersionSaveDate
    Next
End Sub

Sub GetPrecedenceConstraints (strPackageName, dtsPrecedenceConstraints, xmlDocument, xmlPrecedenceConstraints)
    Dim dtsPrecedenceConstraint

    For Each dtsPrecedenceConstraint in dtsPrecedenceConstraints
        Dim xmlPrecedenceConstraint
        Set xmlPrecedenceConstraint = xmlPrecedenceConstraints.insertBefore ( _
            xmlDocument.createElement ("PrecedenceConstraint"), _
            xmlPrecedenceConstraints.selectSingleNode ("PrecedenceConstraint[@StepName > '" & dtsPrecedenceConstraint.StepName & "']"))

        'properties
        xmlPrecedenceConstraint.setAttribute "StepName",    dtsPrecedenceConstraint.StepName

        'collections
        GetProperties   strPackageName, dtsPrecedenceConstraint.Properties, xmlDocument, xmlPrecedenceConstraint.appendChild (xmlDocument.createElement ("Properties"))
    Next
End Sub

Sub GetSteps (strPackageName, dtsSteps, xmlDocument, xmlSteps)
    Dim dtsStep2

    For Each dtsStep2 in dtsSteps
        Dim xmlStep2
        Set xmlStep2 = xmlSteps.insertBefore ( _
            xmlDocument.createElement ("Step2"), _
            xmlSteps.selectSingleNode ("Step2[@Name > '" & dtsStep2.Name & "']"))

        'properties
        xmlStep2.setAttribute "Name",       dtsStep2.Name
        xmlStep2.setAttribute "Description",    dtsStep2.Description

        'collections
        GetProperties           strPackageName, dtsStep2.Properties,        xmlDocument, xmlStep2.appendChild (xmlDocument.createElement ("Properties"))
        GetPrecedenceConstraints    strPackageName, dtsStep2.PrecedenceConstraints, xmlDocument, xmlStep2.appendChild (xmlDocument.createElement ("PrecedenceConstraints"))
    Next
End Sub

Sub GetColumns (strPackageName, dtsColumns, xmlDocument, xmlColumns)
    Dim dtsColumn

    For Each dtsColumn in dtsColumns
        Dim xmlColumn
        Set xmlColumn = xmlColumns.appendChild (xmlDocument.createElement ("Column"))

        GetProperties strPackageName, dtsColumn.Properties, xmlDocument, xmlColumn.appendChild (xmlDocument.createElement ("Properties"))
    Next
End Sub

Sub GetLookups (strPackageName, dtsLookups, xmlDocument, xmlLookups)
    Dim dtsLookup

    For Each dtsLookup in dtsLookups
        Dim xmlLookup
        Set xmlLookup = xmlLookups.appendChild (xmlDocument.createElement ("Lookup"))

        GetProperties strPackageName, dtsLookup.Properties, xmlDocument, xmlLookup.appendChild (xmlDocument.createElement ("Properties"))
    Next
End Sub

Sub GetTransformations (strPackageName, dtsTransformations, xmlDocument, xmlTransformations)
    Dim dtsTransformation

    For Each dtsTransformation in dtsTransformations
        Dim xmlTransformation
        Set xmlTransformation = xmlTransformations.appendChild (xmlDocument.createElement ("Transformation"))

        GetProperties strPackageName, dtsTransformation.Properties, xmlDocument, xmlTransformation.appendChild (xmlDocument.createElement ("Properties"))
    Next
End Sub

Sub GetTasks (strPackageName, dtsTasks, xmlDocument, xmlTasks)
    Dim dtsTask

    For each dtsTask in dtsTasks
        Dim xmlTask 
        Set xmlTask = xmlTasks.insertBefore ( _
            xmlDocument.createElement ("Task"), _
            xmlTasks.selectSingleNode ("Task[@Name > '" & dtsTask.Name & "']"))

        ' The task can be of any task type, and each type of task has different properties.

        'properties
        xmlTask.setAttribute "CustomTaskID",    dtsTask.CustomTaskID
        xmlTask.setAttribute "Name",        dtsTask.Name
        xmlTask.setAttribute "Description", dtsTask.Description

        'collections
        GetProperties strPackageName, dtsTask.Properties, xmlDocument, xmlTask.appendChild (xmlDocument.createElement ("Properties"))

        If dtsTask.CustomTaskID = "DTSDataPumpTask" Then
            GetOLEDBProperties  strPackageName, dtsTask.CustomTask.SourceCommandProperties,     xmlDocument, xmlTask.appendChild (xmlDocument.createElement ("SourceCommandProperties"))
            GetOLEDBProperties  strPackageName, dtsTask.CustomTask.DestinationCommandProperties,    xmlDocument, xmlTask.appendChild (xmlDocument.createElement ("DestinationCommandProperties"))
            GetColumns      strPackageName, dtsTask.CustomTask.DestinationColumnDefinitions,    xmlDocument, xmlTask.appendChild (xmlDocument.createElement ("DestinationColumnDefinitions"))
            GetLookups      strPackageName, dtsTask.CustomTask.Lookups,             xmlDocument, xmlTask.appendChild (xmlDocument.createElement ("Lookups"))
            GetTransformations  strPackageName, dtsTask.CustomTask.Transformations,         xmlDocument, xmlTask.appendChild (xmlDocument.createElement ("Transformations"))
        End If
    Next
End Sub

Sub FormatXML (xmlDocument, xmlElement, intIndent)
    Dim xmlSubElement

    For Each xmlSubElement in xmlElement.selectNodes ("*")
        xmlElement.insertBefore xmlDocument.createTextNode (vbCrLf & String (intIndent + 1, vbTab)), xmlSubElement
        FormatXML xmlDocument, xmlSubElement, intIndent + 1
    Next

    If xmlElement.selectNodes ("*").length > 0 Then
        xmlElement.appendChild xmlDocument.createTextNode (vbCrLf & String (intIndent, vbTab))
    End If
End Sub

Sub GetPackage (strServerName, strPackageName)
    Dim dtsPackage2
    Set dtsPackage2 = CreateObject ("DTS.Package2")

    Dim DTSSQLStgFlag_Default
    Dim DTSSQLStgFlag_UseTrustedConnection

    DTSSQLStgFlag_Default = 0
    DTSSQLStgFlag_UseTrustedConnection = 256

    On Error Resume Next
    dtsPackage2.LoadFromSQLServer strServerName, , , DTSSQLStgFlag_UseTrustedConnection, , , , strPackageName

    If Err.Number = 0 Then
        On Error Goto 0
        'fsoTextStream.WriteLine dtsPackage2.Name

        Dim xmlDocument
        Set xmlDocument = CreateObject ("Msxml2.DOMDocument.3.0")

        Dim xmlPackage2
        Set xmlPackage2 = xmlDocument.appendChild (xmlDocument.createElement ("Package2"))

        'properties
        xmlPackage2.setAttribute "Name", dtsPackage2.Name

        'collections
        GetProperties       strPackageName, dtsPackage2.Properties,     xmlDocument, xmlPackage2.appendChild (xmlDocument.createElement("Properties"))
        GetConnections      strPackageName, dtsPackage2.Connections,    xmlDocument, xmlPackage2.appendChild (xmlDocument.createElement ("Connections"))
        GetGlobalVariables  strPackageName, dtsPackage2.GlobalVariables,    xmlDocument, xmlPackage2.appendChild (xmlDocument.createElement ("GlobalVariables"))
        'SavedPackageInfos only apply to DTS packages saved in structured storage files
        'GetSavedPackageInfos   strPackageName, dtsPackage2.SavedPackageInfos,  xmlDocument, xmlPackage2.appendChild (xmlDocument.createElement ("SavedPackageInfos"))
        GetSteps        strPackageName, dtsPackage2.Steps,      xmlDocument, xmlPackage2.appendChild (xmlDocument.createElement ("Steps"))
        GetTasks        strPackageName, dtsPackage2.Tasks,      xmlDocument, xmlPackage2.appendChild (xmlDocument.createElement ("Tasks"))

        FormatXML xmlDocument, xmlPackage2, 0
        xmlDocument.save strPackageName + ".xml"
    Else
        MsgBox Err.Description, , strPackageName
        On Error Goto 0
    End If
End Sub

Sub Main
    Dim strServerName
    strServerName = Trim (InputBox ("Server:"))

    If strServerName  "" Then
        Dim cnSQLServer 
        Set cnSQLServer = CreateObject ("ADODB.Connection")
        cnSQLServer.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" & strServerName

        Dim rsDTSPackages
        Set rsDTSPackages = cnSQLServer.Execute ("SELECT DISTINCT name FROM sysdtspackages ORDER BY name")

        Dim strPackageNames

        Do While Not rsDTSPackages.EOF
            GetPackage strServerName, rsDTSPackages ("name")
            rsDTSPackages.MoveNext
        Loop

        rsDTSPackages.Close
        set rsDTSPackages = Nothing

        cnSQLServer.Close
        Set cnSQLServer = Nothing

        Dim strCustomTaskIDs
        Dim strCustomTaskID

        MsgBox "Finished", , "GetPackages.vbs"
    End If
End Sub

Main