DTS PACKAGE STEPS MONITORING

Question

Post reply

DTS PACKAGE STEPS MONITORING

Anthony Malone

Mr or Mrs. 500

Points: 538
More actions
June 13, 2005 at 12:59 pm

#66140

I have a process that i need to complete, one objective of the process is to monitor a DTS Package for completion or failure. The package has been scheduled as job, there are seven steps in the dts package each step takes approx 3 minutes to execute. How can i monitor the status of each step in the DTS Package while the job is in progress, to alert me that the entire dts package completed successfully or at which step did the process fail.\

Thanks Anthony

Anthony Malone

Viewing 8 posts - 1 through 7 (of 7 total)

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

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 1

Sql Junkie-204042

Hall of Fame

Points: 3874

June 13, 2005 at 1:44 pm

#565100

You can run a SQL Trace to track the progress of each step in the DTS package.

Or you can create a log file when the packge is executed (DTS package properties / Logging tab)

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 2

I prefer (on my important DTS packages) to create a table to log job steps (something like tblDTSLog, etc.). I add steps in the DTS package itself to write to this log table (one On Success and one On Failure). That way you can query the table, and it maintains history for you. Sometimes I had SQL Server Agent jobs go out and check the log table for errors, etc. and email me the results.

philcart SSC-Forever Points: 47794 More actions · Answer 3

Or for something completely different, you could turn on the inbuilt SQL Server Package Logging. This logs package/step/task execution to tables in the msdb database. From there it's a simple t-sql query to get the results you need.

To check for package completion you just need to query the endtime column in sysdtspackagelog. When the package starts this field is null and is populated when the package completes. It's a very similar query for step execution using the sysdtssteplg table. If you want/need to log custom messages you can use the WriteTaskRecord, or WriteStringToLog methods in an ActiveXScript task. These methods log entries into the sysdtstasklog table.

And guess what, it automatically retains an execution history that is linked to the version of the package that executed.

Another thought, you could actually use the "On Failure" workflow to notify you, instead of constantly watching ti execute.

--------------------
Colt 45 - the original point and click interface

cliffb SSCarpal Tunnel Points: 4547 More actions · Answer 4

We use a combination of methods to track our ETL jobs. We have a custom logging table that logs the start and stop of the package by Lineage Full GUID. This allows us to go into the sysdtsteplog and match up the instance of the package running by the LineageFull column. It's a great time saver for debugging issues with the packages.