Technical Article

Find and Replace VBScript within DTS Package


This script will find and replace specified text within all ActiveXScript tasks with a certain text string in the description. It was designed on SQL 2000, but I think it will run on SQL 7 as it doesn't reference any SQL 2000 specific properties.

It opens a package object and then loops through the steps collection looking for a specific string in the description. I use the step description because this is the main property that users have control over.

When it finds the string in the step description it opens a task object using the taskname associated with the step. Once the task object is opened it does a replace on the ActiveXScript property.

Questions and comments welcome.


' Visual Basic ActiveX Script
'Purpose: Find text within an ActiveXScript and replace it 
' Author: Phillip Carter - 28 Oct 2002
' the string we are looking for in the step description
Const STEP_DESC = "Some Text"

Function Main()

Dim oPackage ' DTS Package object
Dim oStep ' DTS step object
Dim oTask ' DTS task object
Dim nStps ' number of steps in package
Dim nCntr ' counter for looping through steps collection
Dim sScript ' changed text
Dim sFindStr ' find text
Dim sRplStr ' replace text

' Setup find and replace strings
sFindStr = "What we are looking for"
sRplStr = "What we are replacing"

' open DTS package object
Set oPackage = DTSGlobalVariables.Parent

' get count of steps in package
nStps = oPackage.Steps.Count 

' Loop through steps collection using For ... Next loop
For nCntr = 1 To nStps

' use the InStr function to search for the constant within the description
If InStr(1, oPackage.Steps(nCntr).Description, STEP_DESC, vbTextCompare) > 0 Then

' open task object using the taskname associated with the step
Set oTask = oPackage.Tasks(oPackage.Steps(nCntr).TaskName)

' use the Replace function to change the text
sScript = Replace(oTask.Properties("ActiveXScript").Value, sFindStr, sRplStr)

' Assign changed text to back to the property
oTask.Properties("ActiveXScript").Value = sScript

' Close the objects we opened
Set oTask = Nothing
Set oStep = Nothing

End If


' close the package object
Set oPackage = Nothing

' signal execution success
Main = DTSTaskExecResult_Success

End Function