September 16, 2004 at 3:23 pm
Hello
is there any way to perform search for the word in DTS?
I have to go over a lot of servers and check many DTS to find which accessing one specific server. I would search for the name of this server
Thank you in advance
September 17, 2004 at 1:14 am
dts packages are stored in binary format inside of sql server.
You can use the SQL-DMO to walk the server, packages and connections to find the connections you are looking for.
You'll need the DTS.Application Object to get to the SQL Server dts package repository; use the EnumPackageInfos method to read the list of dts packages on the server; Package Object (look under Package Objects, Package in BOL). Use the LoadFromSQLServer() method to retieve a package from sql server.
The DTS samples that come with sql install are a great place to start. there's some code in there for accessing dtspackages with sql-dmo.
Julian Kuiters
juliankuiters.id.au
December 5, 2005 at 4:44 pm
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
April 21, 2006 at 5:47 am
Hello,
I've tried using you very lovely script but am getting an 'Invalid Class String' error after 71 (or 176) dts packages have been successfully scripted.
I can't seem to determine the order in which the dts packages are scripted though, in order to determine which package is causing the error.
It doesn't appear to be working in name or date order - Do you have any suggestions?
Helen
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply