Search in DTS packages
-
Good afternoon,
Could anybody give me the idea if there is a way to search for a concrete phrase from all the DTS packages.
Let's say I have about 100 different DTS packages in my production database; some of them are really complicated and consist of tens of tasks. And I need to find a phrase searching all of them...
Any help will be appreciated.
Alex
-
The packages are stored in the sysdtspackages table as binary objects so you won't be able to query the table for anything.
You can put together a VBScript to open the package and locate what you're looking for.
What "phrase" are you looking for?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
You could save all of the packages to a structured file (text) and then search those files. Kind of a pain, I know.
-
Thanks Bob,
I was thinking about dts files before. But I was hoping about some magic.
Philip, I'm not the first year in this business and I know what sysdtspackages table is. I was asking for the advise, not to teach me the things that are obvious to the student of elementary school. Sometimes it's better not to give any advise than to give one.
-
AER, you make no mention of what you've attempted, or thought of, in your original post. Unfortunately there isn't any mind-reading add-on for this forum so sometimes the obvious is stated more for the community of over 100000, than as a response to the poster.
Had you responded to my question about what you are searching for, I could have responded in more detail with a process that you could follow.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
Sorry AER, I'm with Phill on this one.
I'm fairly new to all things SQL and the smallest snippet of information goes along way to help people like myself get a grip of things and eventually stop asking for 'obvious' answers, (although I admit I'm more of a reader than a poster).
Keep up the good work Phill and all others who have patience with us Newbies.
Kev.
-
Phill,
I don't think it really matters what phrase or word you are you searching for in DTS package; in this case I was searching for '_url' string.
I probably didn't make it specific about what I was thinking and what was done. Sorry about that.
But "put together a VBScript to open the package and locate what you're looking for" - I thought this is obvious to anybody.
I saved all the packages as 'Visual Basic File' in a new folder. Then I opened a Windows Explorer Search and made a search for this folder 'containing text' '_url'
I'm sure that what I did manually can be put in a script. I can see that one of the ways of doing that is to utilize a VBScript & Index Server. I was just wondering if anybody did similar things before and can provide with an example.
I know that I'm not the only one who was expriencing a problem with searching in DTS packages. I think solving it will be a good help for SQLServerCentral members.
I'm still looking for a better decision.
You guys have a Happy Thansgiving.
Alex
-
Have you already tried to search on http://www.sqldts.com ?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
I just made a research on this site and couldn't find anything about this issue.
But I did like the site though; thank you for mentioning it. A lot of good DTS stuff there.
-
AER, have a look at the script I've posted here,
http://qa.sqlservercentral.com/scripts/contributions/569.asp
It does a find and replace within ActiveXScripts. Probably be able to modify it a bit to search other properties or collections in the package.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
Thank you Phill,
This is a nice code for replacing values in one Package.
I'm looking for a code to loop through all the packages, find a value and tell me in what package and step of this package this value resides.
-
I've just posted another VBScript that sets the DTS Package Logging properties for groups of DTS Packages. It's titled "Set logging properties for group of DTS packages". It should be available shortly.
You could hack together something from this script and the one posted previously.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
I've never had any luck saving as structured files, the search tends to not find what I'm looking for, I think (emphasis on think) because many things are stored as unicode.
What I think you might be better off doing is script something to save all the packages as VBScript files, which is another save-as option (and I assume a method call but haven't looked).
At least I think then it is all in plain text that can be searched.
-
Linwood,
That is exactly what I did if you will look at one of my previous replies
-
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 (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
Next
Next
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
Next
End If
Set oFS = Nothing
End Function
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply