SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 export all dts to VB or full text search on DTS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JaredStokes
Starting Member

USA
9 Posts

Posted - 11/14/2003 :  11:22:08  Show Profile  Visit JaredStokes's Homepage  Reply with Quote
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
36928 Posts

Posted - 11/14/2003 :  12:28:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

JaredStokes
Starting Member

USA
9 Posts

Posted - 11/14/2003 :  13:37:53  Show Profile  Visit JaredStokes's Homepage  Reply with Quote
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. . .
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 11/14/2003 :  14:36:41  Show Profile  Visit Stoad's Homepage  Reply with Quote
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
Go to Top of Page

JaredStokes
Starting Member

USA
9 Posts

Posted - 11/14/2003 :  14:43:34  Show Profile  Visit JaredStokes's Homepage  Reply with Quote
I thank you for the help! If I can get it to work I will let you know!

w00t.
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 11/15/2003 :  06:34:13  Show Profile  Visit Stoad's Homepage  Reply with Quote
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
Go to Top of Page

renezito
Starting Member

Chile
1 Posts

Posted - 03/06/2012 :  09:26:27  Show Profile  Reply with Quote
Thanks ver much Stoad, your VB solutions works great. T-SQL didn't work for me.
Thanks again =)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000