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

You might try working with the system table sysdtspackages as demonstrated on sqldts.com in Transferring DTS Packages.
Also, there used to be many tools available for MS SQL 2000 before the new versions proliferated. I found one, called DTS Package Compare, as a free download at Red Gate Labs.

Answer 2

For completeness, I started another VBS script to read an XML file generated by GetPackages.vbs and save it as a DTS package on another SQL Server. This is even less complete, but I hope it will eventually be useful.

PushPackages.vbs

Option Explicit

Sub SetProperties (dtsProperties, xmlProperties)
    dim xmlProperty

    For Each xmlProperty in xmlProperties.selectNodes ("Property[@Set='-1']")
     dtsProperties.Item (xmlProperty.getAttribute ("Name")).Value = xmlProperty.getAttribute ("Value")
    Next
End Sub

Sub SetOLEDBProperties (dtsOLEDBProperties, xmlOLEDBProperties)
    dim xmlOLEDBProperty

    For Each xmlOLEDBProperty in xmlOLEDBProperties.selectNodes ("OLEDBProperty")
     dtsOLEDBProperties.Item (xmlOLEDBProperty.getAttribute ("Name")).Value = xmlOLEDBProperty.getAttribute ("Value")
    Next
End Sub

Sub SetConnections (dtsConnections, xmlConnections)
    dim dtsConnection2
    dim xmlConnection2

    For each xmlConnection2 in xmlConnections.selectNodes ("Connection2")
     set dtsConnection2 = dtsConnections.New (xmlConnection2.getAttribute ("ProviderID"))
     SetProperties  dtsConnection2.Properties,  xmlConnection2.selectSingleNode ("Properties")
     SetOLEDBProperties dtsConnection2.ConnectionProperties, xmlConnection2.selectSingleNode ("ConnectionProperties")
     dtsConnections.Add dtsConnection2
    Next
End Sub

Sub SetGlobalVariables (dtsGlobalVariables, xmlGlobalVariables)
    dim xmlGlobalVariable2

    For Each xmlGlobalVariable2 in xmlGlobalVariables.selectNodes ("GlobalVariable2")
     dtsGlobalVariables.AddGlobalVariable xmlGlobalVariable2.getAttribute ("Name"), xmlGlobalVariable2.getAttribute ("Value")
    Next
End Sub

Sub SetPrecedenceConstraints (dtsPrecedenceConstraints, xmlPrecedenceConstraints)
    dim xmlPrecedenceConstraint
    dim dtsPrecedenceConstraint

    For Each xmlPrecedenceConstraint in xmlPrecedenceConstraints.selectNodes ("PrecedenceConstraint")
     set dtsPrecedenceConstraint = dtsPrecedenceConstraints.New (xmlPrecedenceConstraint.getAttribute ("StepName"))
     SetProperties dtsPrecedenceConstraint.Properties, xmlPrecedenceConstraint.selectSingleNode ("Properties")
     dtsPrecedenceConstraints.Add dtsPrecedenceConstraint
    Next
End Sub

Sub SetSteps (dtsSteps, xmlSteps)
    dim xmlStep2
    dim dtsStep2

    For Each xmlStep2 in xmlSteps.selectNodes ("Step2")
     set dtsStep2 = dtsSteps.New
     SetProperties dtsStep2.Properties, xmlStep2.selectSingleNode ("Properties")
     dtsSteps.Add dtsStep2
    Next

    For Each xmlStep2 in xmlSteps.selectNodes ("Step2")
     set dtsStep2 = dtsSteps.Item (xmlStep2.getAttribute ("Name"))
     SetPrecedenceConstraints dtsStep2.PrecedenceConstraints, xmlStep2.selectSingleNode ("PrecedenceConstraints")
    Next
End Sub

Sub SetTasks (dtsTasks, xmlTasks)
    dim xmlTask
    dim dtsTask

    For Each xmlTask in xmlTasks.selectNodes ("Task")
     set dtsTask = dtsTasks.New (xmlTask.getAttribute ("CustomTaskID"))
     SetProperties dtsTask.Properties, xmlTask.selectSingleNode ("Properties")
     dtsTasks.Add dtsTask
    Next
End Sub

Sub CreatePackage (strServerName, strFileName)
    Dim fsoFileSystem
    set fsoFileSystem = CreateObject ("Scripting.FileSystemObject")

    Dim dtsPackage2
    Set dtsPackage2 = CreateObject ("DTS.Package2")

    Dim DTSSQLStgFlag_Default
    Dim DTSSQLStgFlag_UseTrustedConnection

    DTSSQLStgFlag_Default = 0
    DTSSQLStgFlag_UseTrustedConnection = 256

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

    Dim xmlPackage2
    set xmlPackage2 = xmlDocument.selectSingleNode ("Package2")

    'properties
    SetProperties dtsPackage2.Properties, xmlPackage2.selectSingleNode ("Properties")

    'collections
    SetConnections  dtsPackage2.Connections, xmlPackage2.selectSingleNode ("Connections")
    SetGlobalVariables dtsPackage2.GlobalVariables, xmlPackage2.selectSingleNode ("GlobalVariables")
    SetSteps  dtsPackage2.Steps,  xmlPackage2.selectSingleNode ("Steps")
    SetTasks  dtsPackage2.Tasks,  xmlPackage2.selectSingleNode ("Tasks")

    On Error Resume Next
    dtsPackage2.SaveToSQLServer strServerName, , , DTSSQLStgFlag_UseTrustedConnection

    If Err.Number Then
     MsgBox Err.Description
    End If
End Sub

Sub Main
    Dim strServerName
    Dim strFileName

    If WScript.Arguments.Count  2 Then
     MsgBox "Usage: PushPackages servername filename"
    Else
     strServerName = WScript.Arguments (0)
     strFileName = WScript.Arguments (1)
     CreatePackage strServerName, strFileName
    End If
End Sub

Main

Answer 3

This tool (DTSDoc) does a good job on documenting DTS Packages. It can be run from the command-line which is great to keep documentation up-to-date. It has some positive reviews:

Review by ASP Alliance

Review by Mike Gunderloy (LARKWARE)

Answer 4

Can you please send me the working version of PushPackages.vbs? This one looks like does not do anything

Ram