SQL Server Performance Forum – Threads Archive

delete unused connection in DTS packages

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

What do you mean by unused?

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.

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

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

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.

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.

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.

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.

]]>