Searching DTS Packages for a text string

  • Hello Everyone:

    I am doing a conversion of our data warehouse shadow server to a new shadow server.  The names of the servers are being changes and there is nothing I can do about that.  I have over 180 DTS packages on the shadow server alone, not counting the hundreds of other DTS pacakges on about 20 other servers.

    I need to find all DTS packages that reference the name of the old shadow server in any connection.  The only way I know to do this is to open each and every DTS package via Enterprise Manager and check each connection, validating the connection's server.

    I could output the package to a VB Module, but then again I need to open each DTS package manually.

    Is there a command or set of commands that I could execute to automatically output the packages to files that can then be searched or better yet search them directly and return a list of DTS Package Names that match the filter?

    Yours,

    Scott 

  • I think SpeedFerret or some other 3rd party tool MIGHT be able to do that IF your DTS packages are saved as files.

    IF your DTS packages are saved in MSDB why not search through MSDB and find the string you are looking for?



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • I do not have any third party software.

    msdb..sysdtspackages contain the actual dts package in the column packagedata.  Table Structure follows

     name        sysname             NOT NULL,

     id          uniqueidentifier    NOT NULL,

     versionid   uniqueidentifier    NOT NULL,

     description nvarchar(1024)          NULL,

     categoryid  uniqueidentifier    NOT NULL,

     createdate  datetime                NULL,

     owner       sysname             NOT NULL,

     packagedata image                   NULL,

     owner_sid   varbinary (85)      NOT NULL,

     packagetype int                 NOT NULL

    packagedata is an image datatype and I'm not up on working with image datatypes.  As I understand it the image datatype is used to store binary data which a client application knows how to deal with.

    Your third party software is a good idea.  I was hoping maybe someone knew of some shareware for SQL Server 7.0/2000 DTS.

    Thanks

  • There is a script called sp__SaveDTS, that scans sysdtspackages, and creates a call to export that package to a VB file. Once you have all the packages in text format, you can concatenate all of them and do a search with Notepad or Word.

    Try it.

     

  • C R:

    I couldn't find a stored procedure call sp_SaveDTS or sp__SaveDTS.  Is this a stored procedure created from third party software.  Can you provide the script for creating the stored procedure... without violating any copywrites?

    Thanks,

  • Here's my modified copy; I changed it so it only displays the command to do the export, but you can comment out the line that executes it.

    Change the destination folder (on mine was C:\DTS), and the server name (MYSERVER) and you're good to go.

    Hope this helps.

     

    -- sp__SaveDTS

    -- Downloaded from SQLServerCentral.com

    --Compiled and tested on MYSERVER 10/08/04

    use master

    go

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__SaveDTS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[sp__SaveDTS]

    GO

    set quoted_identifier off

    go

    CREATE proc sp__SaveDTS

    @DTSname varchar(256) = '',

    @Applicationpath varchar(700) ='',

    @destinationpath varchar(700) ='C:\DTS\',

    @switches varchar(200) = ' -E -!X -L C:\DTS\20041008\DTSLog.txt'

    as

    --Created by:MAK

    --Date: Aug 29, 2004

    --Objective: Save all or given DTS package to a folder

    set quoted_identifier off

    set nocount on

    set concat_null_yields_null off

    declare @count int

    declare @folderexist int

    declare @maxcount int

    declare @query varchar(1000)

    declare @date varchar(10)

    declare @versionid varchar(40)

    declare @createdate varchar(25)

    set @date = convert(varchar(10),getdate(),112)

    set @count =1

    Print 'Saving DTS packages - Started'

    print getdate()

    --set @Applicationpath = @Applicationpath +'DTSRUN.exe"'

    set @Applicationpath = 'C:\Progra~1\Micros~3\80\Tools\Binn\DTSRUN.exe'

    create table #DTSTABLE(id int identity(1,1), DTSname varchar(256),

    versionid varchar(40), createdate varchar(25))

    if @dtsname = ''

    begin

    insert into #DTSTABLE (dtsname,versionid,createdate) select name,versionid,replace(replace(convert(varchar(25),createdate,109),':',' '),' ','_')  from

    msdb..sysdtspackages

    --drop table #DTSTABLE

    end

    else

    begin

       insert into #DTSTABLE (dtsname,versionid,createdate) select name,versionid,replace(replace(convert(varchar(25),createdate,109),':',' '),' ','_')  from

    msdb..sysdtspackages where name =@DTSname

    end

    if (select count(*) from #dTStable) = 0

    begin

          set @date = convert(varchar(100), getdate(),109)

          Print 'Error: No valid DTS package found for saving'

    end

    else

    begin

          set @destinationpath = @destinationpath +@date

          create table #files (Files int, Folder int, parent int)

          insert #files exec master.dbo.xp_fileexist @destinationpath

          select @folderexist = Folder from #files

          if @folderexist <>1

          begin

          set @query = 'MKDIR "'+@destinationpath+'"'

          print @query

        exec master..xp_cmdshell @query

          set @destinationpath = @destinationpath

          end

     else

     begin

     print 'Information:'+ @destinationpath + ' already exist. Skipping Folder Creation'

     end

          set @maxcount =  (select max(id) from #dTStable)

    While @count <= @maxcount

    begin

    select @dtsname =dtsname,@versionid=versionid ,@createdate =createdate from #DTSTABLE where id = @count

    --      set @query = ''+@applicationpath +''+ ' -S"'+@@servername+ '" -N"'+@dtsname+'" -V"'+@versionid +'" -F"'+@destinationpath++'\'+@dtsname+'_'+@createdate+'.dts"'+ @switches       set @query = @query

          set @query = ''+@applicationpath +''+ ' -S'+ 'MYSERVER'+ ' -N"'+@dtsname+'" -V"'+@versionid +'" -F"'+@destinationpath++'\'+@dtsname+'_'+@createdate+'.dts"'+ @switches

     set @query = "exec master..xp_cmdshell '" + @query + "'"

          print @query

    -- exec(@query)

    --      exec master..xp_cmdshell @query

    if @@error <> 0

    begin

    Print 'Error'

    end

    set @count = @count+1

    end

    end

    print getdate()

    Print 'Save DTS packages - Completed'

  • C R:

    I am using SQL Server 2000.  I have recreated the procedure and it works just fine with one exception.  The output is a structured storage file and in SQL Server 2000 all structured storage files are encrypted.

    I looked at books online for DTSRun and reviewed the argument switches.  There does not seem to be a switch for output type.

    Any suggestions?

    Did I miss something?

    Scott

    PS.  Here are the arguments from SQL Server 2000 BOL

    /?

    Displays the command prompt options.

    ~

    Specifies that the parameter to follow is hexadecimal text representing the encrypted value of the parameter. Can be used with the /S, /U, /P, /N, /G, /V, /M, /F, and /R options. Using encrypted values increases the security of the command used to execute the DTS package because the server name, password, and so on, are not visible. Use /!Y to determine the encrypted command.

    /S server_name[\instance_name]

    Specifies the instance of SQL Server to connect to. Specify server_name to connect to the default instance of SQL Server on that server. Specify server_name\instance_name to connect to a named instance of SQL Server 2000 on that server.

    /U user_name

    Is a login ID used to connect to an instance of SQL Server.

    /P password

    Is a user-specified password used with a login ID.

    /E

    Specifies a trusted connection (password not required).

    /N package_name

    Is the name of a DTS package assigned when the package was created.

    /G package_guid_string

    Is the package ID assigned to the DTS package when it was created. The package ID is a GUID.

    /V package_version_guid_string

    Is the version ID assigned to the DTS package when it was first saved or executed. A new version ID is assigned to the DTS package each time it is modified. The version ID is a GUID.

    /M package_password

    Is an optional password assigned to the DTS package when it was created.

    /F filename

    Is the name of a structured storage file containing DTS packages. If server_name is also specified, the DTS package retrieved from SQL Server is executed and that package is added to the structured storage engine.

    /R repository_database_name

    Is the name of the repository database containing DTS packages. If no name is specified, the default database name is used.

    /A global_variable_name:typeid=value

    Specifies a package global variable, where typeid = type identifier for the data type of the global variable. The entire argument string can be quoted. This argument can be repeated to specify multiple global variables. See the Remarks section for the different available type identifiers available with global variables.

    To set global variables with this command switch, you must have either Owner permission for the package or the package must have been saved without DTS password protection enabled. If you do not have Owner permission, you can specify global variables, but the values used will be those set in the package, not those specified with the /A command switch.

    /L log_file_name:

    Specifies the name of the package log file.

    /W Windows_Event_Log

    Specifies whether or not to write the completion status of the package execution to the Windows Application Log. Specify True or False.

    /Z

    Indicates that the command line for dtsrun is encrypted using SQL Server 2000 encryption.

    /!X

    Blocks execution of the selected DTS package. Use this command parameter when you want to create an encrypted command line without executing the DTS package.

    /!D

    Deletes the DTS package from an instance of SQL Server. The package is not executed. It is not possible to delete a specific DTS package from a structured storage file. The entire file needs to be overwritten using the /F and /S options.

    /!Y

    Displays the encrypted command used to execute the DTS package without executing it.

    /!C

    Copies the command used to execute the DTS package to the Microsoft Windows® clipboard. This option can also be used in conjunction with /!X and /!Y.

  • Scott,

    DTS Packages are stored as COM structures, therefore you cannot search for texual entities in them.  The Structured Storage File is a collection of COM Structures.. meaning it can contain multiple versions of COM Structures.  Both are binary data.

    The only out of the box method of giving you a psudo representation of the textual aspects of a DTS Package is to save it to a Visual Basic module option.

    I had this same need, so i developed a VB.Net utility that would extract the details of all Connections, Steps, Precedence, Tasks  and all Active-X workflow and Task scripts (and apply line numbering and color-coding to these sections) and export the information to a DHTML Form Control.  The output can be a single or multiple Package selection from Server Storage repository.

    I had never thought of distributing this utility, because it relies heavily on COM Interop to access the SQL-DMO and DTS Package objects and methods.  So you can write a utility that fits your needs, or I would send you a copy of the one i wrote, just don't ask me to support it because I only wrote it for my own needs at the time.

    I have used this utility to extract all stored procedures called from ExecSQL and Active-X scripts in nearly 100 different DTS packages.  I'm not a big fan of reinventing the wheel.. so if you can use it, I'll be happy to send it.

    Mike Gercevich

    mgercevich-nospam@gmail.com (remove '-nospam')

  • Thanks!  I'd like that.  Mt e-mail address is below.

    And if I find it useful so may hundreds of others.  There are sites where you can place a bit of code and charge $5.00 - $10.00 a pop.  Heck, place it on ebay.  It may make you vacation money.

    And if you have other utilities, I've heard of a few people actually making a living at it.  Didn't Bill start off by buying the rights to a little program called DOS.

    srankin1957@yahoo.com

     

  • You're correct Scott; this only exports a Structured file. My mistake, sorry for the waste of time.

    But also thanks to Mike for his help.

    Cesar

  • Scott,

    I have tried to give away my other utility in hopes of having some good feedback sent back to me... I wrote a utility to compare DTS packages between servers, and transfer DTS packages server to server without the need for the structured storage file export.

    I gave it to about 12 different people on here.  One person was nice enough to donate a couple bags of beef jerky (it was outstanding), but nobody else could even offer to drop me a simple line of feedback.

    I highly doubt anyone would actually pay for the utilities I wrote.

    Mike Gercevich

  • Mike:

    I was out Thursday and Friday.  I just read your e-mail.  When I get a chance I will load the utility and try it.  As I have just done I will offer whatever feedback I have after using it.

    And if it solves my problem I'll send you a bag of jerky too!

    Scott

    PS.  You'd be suprised what people will pay for.  They have hospitals to cure what people have paid for.  I was overseas in the Marines in the 70's.

     

  • 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

  • Hi Gordo,

    Great script! It works!

    But it seems keep running after extract 16 DTSs from my server, the rest never get extracted.

    Any idea how it act like this?

    I used Integrated Authentication, in a SAN environment.

    Appreciate yuor work and than you again!

     

    Richard Long

  • Mike,

    If you don't mind, can you also send me a copy of the vb.net utility, i wanted to search thru them myself.

    Please email it me at gouti@hotmail.com

Viewing 15 posts - 1 through 15 (of 17 total)

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