export all dts to VB or full text search on DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

export all dts to VB or full text search on DTS

Author

Topic

JaredStokes
Starting Member

9 Posts

Posted - 2003-11-14 : 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

38200 Posts

Posted - 2003-11-14 : 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

9 Posts

Posted - 2003-11-14 : 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 - 2003-11-14 : 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

9 Posts

Posted - 2003-11-14 : 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 - 2003-11-15 : 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

1 Post

Posted - 2012-03-06 : 09:26:27

Thanks ver much Stoad, your VB solutions works great. T-SQL didn't work for me.
Thanks again =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources