Search DTS for string

Question

Search DTS for string

ewilson10

SSCarpal Tunnel

Points: 4255
More actions
May 11, 2005 at 5:35 pm

#66113

Hello. Does anyone know of a way to search DTS packages for specific strings? I've come across a couple of cool grep scripts in the past but neither covered DTS objects.
Thanks.

Everett Wilson
ewilson10@yahoo.com

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 1

Hello. It's unusual to not get a response on this site so maybe I need to whet the appetite more.

A couple of nice search scripts have come through this web site and combiend they've been really powerful and have served me well, but they've been only partially helpful with my current sitaution.

http://www.sqlservercentral.com/scripts/contributions/1201.asp

http://www.sqlservercentral.com/scripts/contributions/1155.asp

I work in a very decentralized environment where a decision will be coming down the pipe soon to change an important relationship in the Payroll system. The change is certainly needed but it has the side effect of breaking a rarely used realtionship involving the SUBSTRING function. Fortunately I've joined a unit where there isn't a lot of code I need to check for this problem. What I'm really worried about is my old unit where there is a lot of DTS code that will need to be reviewed and additionally it would be good to get a solution out to other MS SQL users that will be effected by this (we have all types of business data servers here, not to mention the reasearch side).

Any help would be greatly appreciated and thanks in advance.

Everett Wilson
ewilson10@yahoo.com

Gordo-174357 Mr or Mrs. 500 Points: 581 More actions · Answer 2

I just submitted a script that makes this easy.

Search for it or here it is:

'**********************************************************************

' Author: Darren Gordon

' Purpose: To script all of your DTS packages out to text files.

' This is useful if you want to search all packages to find

' a specific string. All text from execute SQL tasks and

' activex scripts is included in the output files.

'

' Notes: Set your servername and output folder at the top of

' the script. Also set integrated security on or off.

'************************************************************************

Function Main()

dim sServername

sServername = "MyServerName" '<<<<<<<<<<<<< SET YOUR SERVERNAME HERE! <<<<<<<<<<<<<<

dim DestinationFolder '<<<<<<<<<<<<< Specify an output folder! <<<<<<<<<<<<<<

DestinationFolder = "C:\Documents and Settings\darreng\My Documents\SQL BACKUPS\DTS\" & sServername & "\" & GetDate(Now, "") & "\"

dim bIntegratedSecurity

bIntegratedSecurity=TRUE '***NOTE: If you use standard security, set this to false and specify a username and password below

dim sLogin

sLogin = ""

dim sPassword

sPassword = ""

dim DocFilename

Dim FileSys

set FileSys = CreateObject("Scripting.FileSystemObject")

MakeSureDirectoryTreeExists(DestinationFolder)

Dim Docfile

Dim oApplication ' As DTS.Application

Dim oPackageSQLServer ' As DTS.PackageSQLServer

Dim oPackageInfos ' As DTS.PackageInfos

Dim oPackageInfo ' As DTS.PackageInfo

Dim oPackage ' As DTS.Package

Set oApplication = CreateObject("DTS.Application")

if bIntegratedSecurity then

Set oPackageSQLServer = oApplication.GetPackageSQLServer(sServername,"" ,"" , DTSSQLStgFlag_UseTrustedConnection)

else

Set oPackageSQLServer = oApplication.GetPackageSQLServer(sServername, sLogin, sPassword, 0)

end if

Set oPackageInfos = oPackageSQLServer.EnumPackageInfos("", True, "")

Set oPackageInfo = oPackageInfos.Next

'Note: It is IMPORTANT that oPackage be instantiated and destroyed within the loop. Otherwise,

'previous package info will be carried over and snowballed into a bigger package every time

'this loop is run. That is NOT what you want.

Do Until oPackageInfos.EOF

Set oPackage = CreateObject("DTS.Package2")

'**** INTEGRATED SECURITY METHOD

if bIntegratedSecurity then

oPackage.LoadFromSQLServer sServername, , ,DTSSQLStgFlag_UseTrustedConnection , , , , oPackageInfo.Name

else

'**** STANDARD SECURITY METHOD

oPackage.LoadFromSQLServer sServername, sLogin, sPassword,DTSSQLStgFlag_Default , , , , oPackageInfo.Name

end if

DocFilename = DestinationFolder & oPackageInfo.Name & ".txt"

If FileSys.FileExists(DocFileName) Then FileSys.DeleteFile(DocFileName)

FileSys.CreateTextFile (DocFileName)

set Docfile = FileSys.OpenTextFile (DocFileName,2)

dim oTasks, oProperties

Set oTasks = oPackage.Tasks

For each oTask in oTasks

DocFile.write (vbCrLf)

DocFile.write ("-----TaskDescription:" & oTask.Description)

Set oProperties = oTask.Properties

For Each oProperty In oProperties

If mid(oTask.Description,1,6) <> mid(StepName,1,6) then

DocFile.write (vbCrLf)

DocFile.write ("PropertyName: " & oProperty.Name & " Value=" & oProperty.Value)

end if

DocFile.close

Set DocFile = Nothing

Set oTasks = Nothing

Set oProperties = Nothing

'**** If you want to actually do something to each package (like turn on logging for example) and save them, you could do this here

'oPackage.LogToSQLServer = True

'oPackage.LogServerName = sServername

'oPackage.LogServerUserName = sLogin

'oPackage.LogServerPassword = sPassword

'oPackage.LogServerFlags = 0

'oPackage.SaveToSQLServer sServername, sLogin, sPassword, DTSSQLStgFlag_Default

Set oPackage = Nothing

Set oPackageInfo = oPackageInfos.Next

Loop

'Clean up and free resources

Set oApplication = Nothing

Set oPackageSQLServer = Nothing

Set oPackageInfos = Nothing

Set oPackageInfo = Nothing

Set oPackage = Nothing

Set FileSys = Nothing

Main = DTSTaskExecResult_Success

End Function

Function GetDate(dateVal, delimiter)

'To comply with Option Explict

Dim dateMonth, dateDay

dateVal = CDate(dateVal)

' Converts the dateVal parameter to a date.

' This will cause an error and cause the function

' to exit if the value passed is not a real date.

delimiter = CStr(delimiter)

' Converts the delimiter parameter, which designates

' the delimiting character between the datepart values

' to a string value. If you don't want a delimiting

' character, (such as / or -) then you'd simply pass

' in an empty string for this parameter.

dateMonth = Month(dateVal)

dateDay = Day(dateVal)

GetDate = CStr(Year(dateVal)) & delimiter

If dateMonth < 10 Then

GetDate = GetDate & "0"

End If

GetDate = GetDate & CStr(dateMonth) & delimiter

If dateDay < 10 Then

GetDate = GetDate & "0"

End If

GetDate = GetDate & CStr(dateDay)

End Function

'**********************************************************************

Function MakeSureDirectoryTreeExists(dirName)

'**********************************************************************

Dim oFS, aFolders, newFolder, i

Set oFS = CreateObject("Scripting.FileSystemObject")

' Check the folder's existence

If Not oFS.FolderExists(dirName) Then

' Split the various components of the folder's name

aFolders = split(dirName, "\")

' Get the root of the drive

newFolder = oFS.BuildPath(aFolders(0), "\")

' Scan the various folder and create them

For i = 1 To UBound(aFolders)

newFolder = oFS.BuildPath(newFolder, aFolders(i))

If Not oFS.FolderExists(newFolder) Then

oFS.CreateFolder newFolder

End If

Set oFS = Nothing

End Function

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 3

Too cool! I'll hang on to this one.

The original problem actually never showed up. Too many people complained causing the change to be postponed indefinitely. Yet another example of bad practices coming back to cause unforseen problems.

Thanks

Everett Wilson
ewilson10@yahoo.com

D M-310896 SSC Rookie Points: 27 More actions · Answer 4

Thanks, this is exactly what I was looking for!!

BIG UPs to Darren

D O S SSC Rookie Points: 41 More actions · Answer 5

D O S

SSC Rookie

Points: 41

January 11, 2007 at 5:41 am

#682655

Very useful script indeed

Thanks Darren

pickle-head Valued Member Points: 61 More actions · Answer 6

There's a tool which will search all the DTS packages on all your servers. It does text, wildcard, and regex searches. Check it out here: http://www.dtspowersearch.com

Vijendran SSC Veteran Points: 278 More actions · Answer 7

Does anyone have similiar kind of script/method to search a string on SSIS package

lovesallubhai SSC Rookie Points: 47 More actions · Answer 8

Hello,

I am new to SQL Server. I tried executing the scripts given above with slight modification like specifying the folder name and the servername and it throws too many errors. Can someone help me execute this script.

Thank You

chlavien Valued Member Points: 58 More actions · Answer 9

can i use it for search on one package?

what i need to change?

where can i write the name of the package?

erik-brakke SSC Journeyman Points: 83 More actions · Answer 10

If you have characters in your package names that are not safe for the filesystem you have to change them. The ones I ran into were \ , / , and :

This fixes it

DocFilename = DestinationFolder & replace(replace(replace(oPackageInfo.Name,"/","-"),"\","-"),":","-") & ".txt"

Thanks Darren for the script

renezito Grasshopper Points: 15 More actions · Answer 11

The code in T-SQL doesn't work for me.

I found this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30598

I then modified according to my needs:

I uploaded here the VB solution:

http://www.uploadstation.com/file/qvemdYR

Thanks!

René Pereira Flores

renezito [arroba] gmail.com