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