SQL Server Performance

delete unused connection in DTS packages

Discussion in 'SQL Server DTS-Related Questions' started by houbepa, Sep 6, 2005.

  1. houbepa New Member

    How can I delete, remove all unused connection and transformation task in my DTS packages?

    Regards

    Patrick
  2. dineshasanka Moderator

    What do you mean by unused?
  3. houbepa New Member

    By unused I mean existing connection due to DTS copy of one other but not used in the current DTS process flow. However, if we go through the menu Package/Disconnected Edits... we can see all existing connections even those that are not used in the concerned DTS.

    For example in one of my DTS I have 25 connections but only 3 are really used. And for the DTS transformation tasks, it is the same.

    Is there any script that can detect and delete unused tasks and connections of all DTS packages? When I check the msdb.sysdtspackage but the package data is stored as a binary and the is no way to access the connections or the tasks.

  4. brillo New Member

    This will do what you need:



    '**********************************************************************
    '* Clean Up Unused Connections
    '**********************************************************************
    '* Author:
    '* Lane Tharp
    '* CountryWide Financial Services
    '* Performance Management Group
    '* Plano, Texas
    '* 2004/12/31
    '* ----------------------------------------
    '* Description:
    '* This Script loops through every connection in a package and removes it if unused.
    '* ----------------------------------------
    '* Useage:
    '* 1. Add an ActiveX Script Task to Package being renamed in DTS Designer.
    '* 2. Disable this Task in workflow properties so it does not run with package.
    '* 3. Copy this script into the ActiveX Task.
    '* 5. Make a backup copy of the package. (Do not skip this step)
    '* 6. Save the package. (Do not skip this step)
    '* 7. Execute the ActiveX script in DTS Designer, save, and close the package.
    '*-------------------------------------------------------------------------------------
    '* Debug Note:
    '* - This script is not intended for unnattended execution.
    '* - Do not edit scripts in the ActiveX Task Properties in DTS. (You will go blind.)
    '* - Edit as .vbs file in, and *debug* scripts in the Microsoft Script Editor.
    '-------------------------------------------------------------------------------------
    Function Main()
    On Error Resume Next
    Stop
    Dim loPackage
    Dim loConnections
    Dim loConnection
    Dim loTasks
    Dim loTask
    Dim loProperties
    Dim loProperty
    Dim lbConnectionUsed
    Set loPackage = DTSGlobalVariables.Parent
    Set loConnections = loPackage.Connections
    Set loTasks = loPackage.Tasks
    ' Restart After Collection Change
    Do
    ' Loop Through Connections
    For Each loConnection In loConnections
    lbConnectionUsed = False
    ' Loop Through Tasks
    For Each loTask in loTasks
    Set loProperties = loTask.Properties
    ' Loop Through Properties
    For Each loProperty in loProperties
    If Instr(loProperty.Name, "ConnectionID") Then
    If loProperty = loConnection.ID Then
    lbConnectionUsed = True
    Exit For
    End If
    End If
    Next
    If lbConnectionUsed = True Then
    Exit For
    End If
    Next
    If lbConnectionUsed = False Then
    loConnections.Remove loConnection.Name
    Exit For
    End if
    Next
    If lbConnectionUsed = True Then
    Exit Do
    End If
    Loop
    Set loPackage = Nothing
    Set loConnections = Nothing
    Set loConnection = Nothing
    Set loTasks = Nothing
    Set loTask = Nothing
    Main = DTSTaskExecResult_Success
    End Function

  5. Raulie New Member

    Have you tried checking the "Close connection upon completion option" under workflow properties?

    Raulie


  6. deighvyd New Member

    I have been having this exact same problem and stumbled onto a rather ridiculous solution by accident.

    Basically you need to open the package in DTS Designer. Add a connection to the package and select the existing connection that you wish to delete. Then delete the connection again. When you do a Disconnected Edit on the package you can see that the connection is gone.

    Not a pretty solution but it works and appeals to me more than wasting time writing a VB app to modify my DTS packages.
  7. satya Moderator

    I would go with Brillo suggestion and check SQLDTS website also for such tips.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. mmarovic Active Member

    Good practice in dts design is to have graphical representation of all connection at the beginning of the package not used in any task. Then you create tasks allways using existing connection. That way you can easily redirect your package from dev to qa and from qa to prod environment. It would also help to solve your problem.
  9. adriant42 New Member

    I have a similar problem but with unused steps. They stay as precedence constraints although I have deleted them.

    Here's the detail:

    I saved as a package that has a similiar structure with
    the one I am currently trying to build. Then I changed all task/steps' names,
    queries, servers, workflows, etc to reflect the new structure. Here's what
    happened. When I looked at the disconnected edit, old/original workflows (the
    ones from the source package) stayed in there although I have deleted all
    workflows, i.e. visually there's no workflow left. Or in other words, there
    are two precedence steps on each step (assuming there's only one directional
    precedence flow) when I add new workflows. This problem has caused my package
    stops running after the first task. I have checked individual tasks and they
    work fine. Only when I execute the package in its entirety, it keeps failing
    on me. Any help on how get rid of those old workflows? Of course, the
    ultimate solution would be to build the package from scratch.

    I am not familiar at all with objects in DTS. Does anyone have a code similar to brillo's but for steps.

Share This Page