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

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:

What are those open source tools?

Dainius S.,

"Satya SKJ" wrote:

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 ,
[ConnectedImmediate] [bit] NULL ,
[Connected] [bit] NULL ,
[InUse] [bit] NULL ,
[LastOwnerTaskName] [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 ,
[ConnectionTimeOut] [int] NULL ,
[ProviderID] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NULL ,
[Catalog] [varchar] (30) COLLATE Traditional_Spanish_CI_AS NULL ,
[UseTrustedConnection] [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 ,
[CreatorComputerName] [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 ,
[StepDescription] [varchar] (100) COLLATE Traditional_Spanish_CI_AS NULL ,
[ExecutionStatus] [int] NULL ,
[ExecutionResult] [int] NULL ,
[TaskName] [varchar] (100) COLLATE Traditional_Spanish_CI_AS NULL ,
[CommitSuccess] [bit] NULL ,
[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 ,
[AddGlobalVariables] [bit] NULL ,
[RelativePriority] [int] NULL ,
[CloseConnection] [bit] NULL ,
[ExecuteInMainThread] [bit] NULL ,
[IsPackageDSORowset] [bit] NULL ,
[JoinTransactionIfPresent] [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 ,
[SourceConnectionID] [int] NULL ,
[SourceObjectName] [varchar] (75) COLLATE Traditional_Spanish_CI_AS NULL ,
[SourceSQLStatement] [varchar] (1000) COLLATE Traditional_Spanish_CI_AS
NULL ,
[DestinationConnectionID] [int] NULL ,
[DestinationObjectName] [varchar] (75) COLLATE Traditional_Spanish_CI_AS
NULL ,
[DestinationSQLStatement] [varchar] (1000) COLLATE
Traditional_Spanish_CI_AS NULL ,
[ProgressRowCount] [int] NULL ,
[MaximumErrorCount] [int] NULL ,
[ExceptionFileName] [varchar] (100) COLLATE Traditional_Spanish_CI_AS NULL ,
[FetchBufferSize] [int] NULL ,
[UseFastLoad] [bit] NULL ,
[InsertCommitSize] [int] NULL ,
[ExceptionFileColumnDelimiter] [char] (5) COLLATE Traditional_Spanish_CI_AS
NULL ,
[ExceptionFileRowDelimiter] [char] (5) COLLATE Traditional_Spanish_CI_AS
NULL ,
[AllowIdentityInserts] [bit] NULL ,
[FirstRow] [int] NULL ,
[LastRow] [int] NULL ,
[FastLoadOptions] [int] NULL ,
[RowsComplete] [int] NULL ,
[RowsInError] [int] NULL ,
[ExceptionFileOptions] [int] NULL ,
[ExceptionFileTextQualifier] [varchar] (50) COLLATE
Traditional_Spanish_CI_AS NULL ,
[InputGlobalVariableNames] [varchar] (100) COLLATE
Traditional_Spanish_CI_AS NULL ,
[CommandTimeOut] [int] NULL ,
[ConnectionId] [int] NULL ,
[OutputAsRecordset] [bit] NULL ,
[OutputGlobalVariableNames] [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 ,
[AddGlobalVariables] [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: