MS SQL Server DTS - Search for text string within DTS SQL code

Author

Search for text string within DTS SQL code

D''Animal

2006-03-28, 8:27 pm

Hello all,

I have 100 DTS packages and I am looking for particular text string (for
example:
like '%table_name%').
How can I look for it without opening each DTS and going through every step?
I'd like to perform simmilar search to the one that we do to find text
within 100s of stored procedures using sysobjects and syscomments tables.

Where does SQL server store DTS Sql code?

Thanks
D

Satya SKJ

2006-03-29, 7:31 am

All the DTS package details will be stored in MSDB under SYSDTSPACKAGES, but
I'm not sure whether you can get required details with a search string. If
the dts packages are saved to a file then you can get few open source tools
to search thru those files.
--
-----------------
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.

"D''Animal" wrote:

> Hello all,
>
> I have 100 DTS packages and I am looking for particular text string (for
> example:
> like '%table_name%').
> How can I look for it without opening each DTS and going through every step?
> I'd like to perform simmilar search to the one that we do to find text
> within 100s of stored procedures using sysobjects and syscomments tables.
>
> Where does SQL server store DTS Sql code?
>
> Thanks
> D

D''Animal

2006-03-29, 9:29 am

What are those open source tools?

Dainius S.,

"Satya SKJ" wrote:
[color=darkred]
> All the DTS package details will be stored in MSDB under SYSDTSPACKAGES, but
> I'm not sure whether you can get required details with a search string. If
> the dts packages are saved to a file then you can get few open source tools
> to search thru those files.
> --
> -----------------
> Satya SKJ
> Visit http://www.sql-server-performance.com for tips and articles on
> Performance topic.
>
>
> "D''Animal" wrote:
>

Enric

2006-03-30, 7:31 am

Hi D''Animal,
We've got around 600 dts running in a Active-Active cluster. I did an Vb6
app which take advantage of dtspkg.dll.
I built a process which take each DTS and once open, using FOR...EACH
clauses store all the information into tables.
So this way I've got in each table info about tasks, steps, connections and
global variables and I am be able to look for any string that any dts own.

I enclose you the full script for these tables, hope helps:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VISDTS_Conn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VISDTS_Conn]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VISDTS_Dts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VISDTS_Dts]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VISDTS_Pas]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VISDTS_Pas]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VISDTS_Task]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VISDTS_Task]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[VisDts_Var]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VisDts_Var]
GO

CREATE TABLE [dbo].[VISDTS_Conn] (
[DtsId] [int] NULL ,
[ConnectionId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (75) COLLATE Traditional_Spanish_
CI_AS NULL ,
[Description] [varchar] (200) COLLATE Traditional_Spanish_
CI_AS NULL ,
[ID] [int] NULL ,
[Reusable] [bit] NULL ,
& #91;ConnectedImmedia
te] [bit] NULL ,
[Connected] [bit] NULL ,
[InUse] [bit] NULL ,
& #91;LastOwnerTaskNam
e] [varchar] (25) COLLATE Traditional_Spanish_
CI_AS NULL ,
[DataSource] [varchar] (90) COLLATE Traditional_Spanish_
CI_AS NULL ,
[UserID] [char] (15) COLLATE Traditional_Spanish_
CI_AS NULL ,
[Password] [char] (10) COLLATE Traditional_Spanish_
CI_AS NULL ,
& #91;ConnectionTimeOu
t] [int] NULL ,
[ProviderID] [varchar] (15) COLLATE Traditional_Spanish_
CI_AS NULL ,
[Catalog] [varchar] (30) COLLATE Traditional_Spanish_
CI_AS NULL ,
& #91;UseTrustedConnec
tion] [bit] NULL ,
[InTransaction] [bit] NULL ,
[UseDSL] [bit] NULL ,
[UDLPath] [varchar] (75) COLLATE Traditional_Spanish_
CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VISDTS_Dts] (
[DtsId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (75) COLLATE Traditional_Spanish_
CI_AS NULL ,
[Owner] [varchar] (75) COLLATE Traditional_Spanish_
CI_AS NULL ,
[PackageID] [varchar] (225) COLLATE Traditional_Spanish_
CI_AS NULL ,
[VersionID] [varchar] (225) COLLATE Traditional_Spanish_
CI_AS NULL ,
[CreationDate] [datetime] NULL ,
& #91;CreatorComputerN
ame] [varchar] (25) COLLATE Traditional_Spanish_
CI_AS NULL ,
[Description] [varchar] (100) COLLATE Traditional_Spanish_
CI_AS NULL ,
[LogFileName] [varchar] (255) COLLATE Traditional_Spanish_
CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VISDTS_Pas] (
[DtsId] [int] NULL ,
[StepId] [int] IDENTITY (1, 1) NOT NULL ,
[StepName] [varchar] (100) COLLATE Traditional_Spanish_
CI_AS NULL ,
& #91;StepDescription]
[varchar] (100) COLLATE Traditional_Spanish_
CI_AS NULL ,
& #91;ExecutionStatus]
[int] NULL ,
& #91;ExecutionResult]
[int] NULL ,
[TaskName] [varchar] (100) COLLATE Traditional_Spanish_
CI_AS NULL ,
[CommitSuccess] [bit] NULL ,
& #91;RollbackFailure]
[bit] NULL ,
[ActiveXScript] [varchar] (6000) COLLATE Traditional_Spanish_
CI_AS NULL ,
[ScriptLanguage] [varchar] (20) COLLATE Traditional_Spanish_
CI_AS NULL ,
[FunctionName] [varchar] (20) COLLATE Traditional_Spanish_
CI_AS NULL ,
& #91;AddGlobalVariabl
es] [bit] NULL ,
& #91;RelativePriority
] [int] NULL ,
& #91;CloseConnection]
[bit] NULL ,
& #91;ExecuteInMainThr
ead] [bit] NULL ,
& #91;IsPackageDSORows
et] [bit] NULL ,
& #91;JoinTransactionI
fPresent] [bit] NULL ,
[StartTime] [datetime] NULL ,
[FinishTime] [datetime] NULL ,
[ExecutionTime] [real] NULL ,
[DisableStep] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VISDTS_Task] (
[DtsId] [int] NULL ,
[TaskId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Traditional_Spanish_
CI_AS NULL ,
[Description] [varchar] (50) COLLATE Traditional_Spanish_
CI_AS NULL ,
& #91;SourceConnection
ID] [int] NULL ,
& #91;SourceObjectName
] [varchar] (75) COLLATE Traditional_Spanish_
CI_AS NULL ,
& #91;SourceSQLStateme
nt] [varchar] (1000) COLLATE Traditional_Spanish_
CI_AS
NULL ,
& #91;DestinationConne
ctionID] [int] NULL ,
& #91;DestinationObjec
tName] [varchar] (75) COLLATE Traditional_Spanish_
CI_AS
NULL ,
& #91;DestinationSQLSt
atement] [varchar] (1000) COLLATE
Traditional_Spanish_
CI_AS NULL ,
& #91;ProgressRowCount
] [int] NULL ,
& #91;MaximumErrorCoun
t] [int] NULL ,
& #91;ExceptionFileNam
e] [varchar] (100) COLLATE Traditional_Spanish_
CI_AS NULL ,
& #91;FetchBufferSize]
[int] NULL ,
[UseFastLoad] [bit] NULL ,
& #91;InsertCommitSize
] [int] NULL ,
& #91;ExceptionFileCol
umnDelimiter] [char] (5) COLLATE Traditional_Spanish_
CI_AS
NULL ,
& #91;ExceptionFileRow
Delimiter] [char] (5) COLLATE Traditional_Spanish_
CI_AS
NULL ,
& #91;AllowIdentityIns
erts] [bit] NULL ,
[FirstRow] [int] NULL ,
[LastRow] [int] NULL ,
& #91;FastLoadOptions]
[int] NULL ,
[RowsComplete] [int] NULL ,
[RowsInError] [int] NULL ,
& #91;ExceptionFileOpt
ions] [int] NULL ,
& #91;ExceptionFileTex
tQualifier] [varchar] (50) COLLATE
Traditional_Spanish_
CI_AS NULL ,
& #91;InputGlobalVaria
bleNames] [varchar] (100) COLLATE
Traditional_Spanish_
CI_AS NULL ,
[CommandTimeOut] [int] NULL ,
[ConnectionId] [int] NULL ,
& #91;OutputAsRecordse
t] [bit] NULL ,
& #91;OutputGlobalVari
ableNames] [varchar] (120) COLLATE
Traditional_Spanish_
CI_AS NULL ,
[SQLStatement] [varchar] (2500) COLLATE Traditional_Spanish_
CI_AS NULL ,
[FunctionName] [varchar] (30) COLLATE Traditional_Spanish_
CI_AS NULL ,
[ScriptLanguage] [varchar] (20) COLLATE Traditional_Spanish_
CI_AS NULL ,
& #91;AddGlobalVariabl
es] [bit] NULL ,
[ActiveXScript] [varchar] (2000) COLLATE Traditional_Spanish_
CI_AS NULL ,
[Type] [varchar] (40) COLLATE Traditional_Spanish_
CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[VisDts_Var] (
[DtsId] [char] (10) COLLATE Traditional_Spanish_
CI_AS NULL ,
[Variables] [varchar] (3000) COLLATE Traditional_Spanish_
CI_AS NULL
) ON [PRIMARY]
GO

--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)

"D''Animal" wrote:
[color=darkred]
> What are those open source tools?
>
> Dainius S.,
>
> "Satya SKJ" wrote:
>

Sponsored Links