Monitoring DTS Packages

Question

Post reply

Monitoring DTS Packages

lucky-80472

SSCertifiable

Points: 7793
More actions
June 16, 2004 at 1:19 am

#59665

Hi Gurus,
Is there any way to monitor DTS packages ? as we do for queries etc by running a profiler. As I have to monitor the remote servers, thought of using the profiler which will give the required information.
If there is any possibility of monitoring the DTS packages, what will be the event classes and data columns to be selected.
Any help would be appreciated.
Thanks in advance.

Lucky

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Not that I am aware of. Might try logging to a table or file thru package properties. That will give you a good idea.

ghughes SSCrazy Points: 2201 More actions · Answer 2

I monitor 127 jobs that are run daily, and so far the best way has been created BEGIN job step that stamps a table, and a END job step. I wrote a ASP page that read the table and looks to make sure the daily job ran, if not it list the job and I go check it out.

I use this same logic for Weekly and Month jobs and it works very well. Here is the status table

CREATE TABLE [Status] (

[JOBNAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[LASTRUN] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[DESCRIP] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[SEQ] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[JobActive] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Sched] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BeginTime] [datetime] NULL ,

[EndTime] [datetime] NULL ,

CONSTRAINT [PK_BarStatus] PRIMARY KEY CLUSTERED

(

[SEQ]

&nbsp WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

GO

The Descrip field is a 2 line description of the job, schedule either has a 0 = daily job, or 1 thru 7 for weekly job, M for monthly job.

Darrell Parrish SSC Enthusiast Points: 181 More actions · Answer 3

There are also a series of "sys" tables in MSDB that you could check against.

Darrell Parrish
La Crosse, WI

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 4

Check out the related post at...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=120942

I personally use a DTS to call and execute another DTS. This method allows me to log each step of the called DTS after it has finished processing. I log (write to a table) the DTS name, step name, step description, status (success/failure) and the start and end times.