-------------------------------------------------------
-- search_in_dts2000.sql
-- ---------------------
--
-- Searching in DTS-packages for a searchstring in SQL-Tasks
--
-- script has to run on that server that holds the packages
--
-- Version 1.0
-- 25.06.2007
-- Webfred
-- (Adapted from an internet-script)
--
-- Known problems
-- ------------------
-- Properties bigger than 8000 chars
-- can't be searched.
--
-- see also known problems here:
-- (http://support.microsoft.com/kb/325492/en-us)
--------------------------------------------------------------------
declare @search_for varchar(255)
declare @verbose int
declare @server varchar(255)
declare @user varchar(255)
declare @pass varchar(255)
---------------------------------------
-- searchstring:
---------------------------------------
select @search_for = 'hs_artnr'

------------------------------------------------------
-- Verbose: (more or less prints)
------------------------------------------------------
select @verbose = 1 -- 1 = more
                    -- 0 = less

-----------------------------------------------------
-- Credentials:
-- (Or use Windows Authentication: see section "load packages")
-----------------------------------------------------
select @server = 'servername'
select @user = 'sa'
select @pass = 'password'

set nocount on

DECLARE @object int
DECLARE @pkgname nvarchar(255)
declare @rc  int
DECLARE @src varchar(255)
Declare @desc varchar(255)
Declare @Numof int
Declare @NumofItems int
declare @i int
declare @j int
Declare @property varchar(8000)
Declare @property_value varchar(8000)
Declare @property_name varchar(8000)
declare @counter int
select @counter=0


-------------------------------------------------------
-- write list of all packages in temp table
-------------------------------------------------------
if exists(select * from tempdb.dbo.sysobjects where name like '#dts_package____%')
  drop table #dts_packages
create table #dts_packages 
   (name varchar(1000),
    id uniqueidentifier,
    versionid uniqueidentifier,
    description varchar(1000),
    createdate datetime,
    owner varchar(100),
    size  int,
    packagedata image, 
    isowner varchar(100),
    packagetype int
    )

insert into #dts_packages exec msdb..sp_enum_dtspackages

print '*** searching for <'+@search_for+'> in Sql-Statements ***'

-------------------------------------------------------
-- all packages in a loop
-------------------------------------------------------
while (select count(*) from #dts_packages) > 0
begin
  select top 1 @pkgname=name from #dts_packages order by name
  delete from #dts_packages where name = @pkgname

if @verbose = 1
Print '**************** checking now package ' + rtrim(@pkgname) +' ****************'

-------------------------------------------------------
--create object from package
-------------------------------------------------------
 EXEC @rc = sp_OACreate 'DTS.Package', @object OUTPUT
  IF @rc <> 0 goto PrintError

-------------------------------------------------------
-- load package
-------------------------------------------------------
 EXEC @rc = sp_OAMethod @object, 'LoadFromSQLServer',
-- SQL Server Authentication
           NULL,@server,@user,@pass,'0','','','',@pkgname
-- Windows Authentication
--           NULL,@server,'','','256','','','',@pkgname
          
  IF @rc <> 0 goto PrintError


-------------------------------------------------------
-- cont of tasks in package
-------------------------------------------------------
  EXEC @rc = sp_OAGetProperty @object, 'Tasks.Count', @Numof OUT
  IF @rc <> 0 goto PrintError
  set @i = 0

-------------------------------------------------------
-- work on each task in a loop
-------------------------------------------------------
  While @i < @Numof 
  begin 
    set @i = @i + 1
 
-------------------------------------------------------
-- count of properties in a Task
-------------------------------------------------------
    set @property = 'Tasks(' + rtrim(cast(@i as char)) + 
                         ').Properties.Count'
    EXEC @rc = sp_OAGetProperty @object, @property, @NumofItems OUT
    IF @rc <> 0 goto PrintError
   
-------------------------------------------------------
-- work on each property in a loop
-------------------------------------------------------
    set  @j = 0 
    while @j < @NumofItems
    begin 
      set @j = @j + 1

-------------------------------------------------------
-- get the name of the property
-------------------------------------------------------
      set @property = 'Tasks(' + rtrim(cast(@i as char)) + 
                           ').Properties(' +
                     rtrim(cast(@j as char)) + ').Name'
      EXEC @rc = sp_OAGetProperty @object, @property, @Property_name OUT

      IF @rc <> 0 goto PrintError
 
-------------------------------------------------------
-- get the value of the property
-----------------------------------------------------
-- But only for SQL
-----------------------------------------------------
      if isnull(@Property_name,'bla') like '%SQLStatement%'
      begin
        set @property = 'Tasks(' + rtrim(cast(@i as char)) + 
                             ').Properties(' +
                         rtrim(cast(@j as char)) + ').Value'
  
        EXEC @rc = sp_OAGetProperty @object, @property, @property_value OUT
  
        --IF @rc <> 0 goto PrintError / not goto, print error and go on
        if @rc <> 0
           begin
             print 'Problem <'+@pkgname+'> Task No '+convert(varchar,@i)+' can''t be searched'
             select @rc=0
             select @property_value=''
           end
        if charindex(@search_for,isnull(@property_value,'bla')) > 0
        begin 
          print '-->Hit in <'+@pkgname+'> Task No '+convert(varchar,@i)
          select @counter=@counter+1
          IF @rc <> 0 goto PrintError
        end
      end
-------------------------------------------------------
    end -- End of Propertyloop
-------------------------------------------------------

-------------------------------------------------------
  end -- End of Tasksloop
-------------------------------------------------------  

exec @rc = sp_OADestroy @object --release object!!!!

-------------------------------------------------------
end -- End of packageloop
-------------------------------------------------------

print 'Hit count: '+convert(varchar,@counter)
return

-----------------
-- error routine
-----------------
PrintError:

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT rc=convert(varbinary(4),@rc), Source = @src, Description = @desc