| Author |
 Topic  |
|
|
JaredStokes
Starting Member
USA
9 Posts |
 Posted - 11/14/2003 : 11:22:08
|
I need to do a simple text search for a particular sting in all my DTS packages. I thought if I could export them all to VB (.bas) files I could then do my search. But I have well over 100 DTS packages. Opening and saving each one as a VB file is taking longer then I have time. If anyone knows of a way to bulk export all of my DTS packages to VB files or do the text search please let me know.
Thanks!
Jared |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 11/14/2003 : 12:28:18
|
The DTS packages are stored in sysdtspackages in the msdb database. You might be able to do something with the table for the search. Packagedata column is where the code is, but it is in binary format. Maybe it could be converted?
Tara |
 |
|
|
JaredStokes
Starting Member
USA
9 Posts |
Posted - 11/14/2003 : 13:37:53
|
Thanks for the location. I am not aware of a way to convert that information into a ascii readable format. *sigh*
Back to double click . . . save as. . . |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 11/14/2003 : 14:36:41
|
It must work. Tweaking and correcting I leave to you.
use msdb
GO
declare @name varchar(50), @i int, @s varchar(80)
set @name='' set @i=0 set @s='My String'
set @s=cast(cast(@s as varbinary(80)) as varchar(80))
while @name is not null
begin
select @name=min(name) from sysdtspackages where name>@name
while @i<(select top 1 datalength(packagedata)/8000.0 from sysdtspackages
where name=@name)
begin
if charindex(@s, (select top 1 substring(packagedata,1+@i*8000,8000) from
sysdtspackages where name=@name))>0
print 'String found in package '+@name
set @i=@i+1
end
set @i=1
end |
|
|
|
JaredStokes
Starting Member
USA
9 Posts |
Posted - 11/14/2003 : 14:43:34
|
I thank you for the help! If I can get it to work I will let you know!
w00t. |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 11/15/2003 : 06:34:13
|
Maybe better via ADO:
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, i, s
cn.Open "provider=sqloledb;data source=(local);" & _
"initial catalog=msdb;user id=sa;password="
rs.Open "select packagedata, name, versionid from sysdtspackages", _
cn, adOpenForwardOnly, adLockReadOnly, adCmdText
While Not rs.EOF
i = 0
s = ""
While i < rs(0).ActualSize
s = s & rs(0).GetChunk(8000)
i = i + 8000
Wend
If InStr(s, "What I am searching for") > 0 Then
MsgBox "String found in package " & rs(1) & " " & rs(2)
End If
rs.MoveNext
Wend
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing |
|
|
|
renezito
Starting Member
Chile
1 Posts |
Posted - 03/06/2012 : 09:26:27
|
Thanks ver much Stoad, your VB solutions works great. T-SQL didn't work for me.
Thanks again =)
|
|
|
| |
Topic |
|
export all dts to VB or full text search on DTS
