Latest DTS Package Log

,

This script returns the latest package log for the DTS package specified in the @package_name variable.  The interface for viewing DTS package logs through Enterprise Manager is limited in the fact that it doesn't provide textual descriptions of the values for the Step Execute Status and Step Execute Results.  This makes it difficult to trace what caused a package error.  This script will also return the number of rows effected by data transformation tasks, which is not available through Enterprise Manager.

Run this script while waiting for a scheduled package to execute to view the package progress.  We use this script to save run results to a file.

This script will only return results if DTS Package logging has been enabled on the Logging tab of the package properties.  The server this script is run from must be the same server specified in the package logging properties of the package.

USE msdb

/*
| File Name: sel_latest_package_log.sql
|
| Description: Displays the latest package log for the package specified in
|              @package_name
|
| Modification History
| ====================
| Created: 22-Aug-02 Created By: Mark Cudmore
|
| Modified Date 	Modified By		Change(s)
| =============== ============== ===================================================
|
*/

DECLARE @package_name sysname

SET @package_name = 'dnc_extr_new_dnc_accounts'

SELECT 
   dsl.stepname AS [Step Name]
   , CASE dsl.stepexecstatus
         WHEN 1 THEN 'DTSStepExecStat_Waiting'
         WHEN 2 THEN 'DTSStepExecStat_InProgress'
         WHEN 3 THEN 'DTSStepExecStat_Inactive'
         WHEN 4 THEN 'DTSStepExecStat_Completed'
     END AS [Step Execute Status]
   , CASE dsl.stepexecresult
         WHEN 0 THEN 'DTSStepExecResult_Success'
         WHEN 1 THEN 'DTSStepExecResult_Failure'
     END AS [Step Execute Result]
   , dsl.starttime AS [Start Time]
   , dsl.endtime AS [End Time]
   , dsl.elapsedtime AS [Elapsed Time]
   , dsl.progresscount AS [Row Count]
   , dsl.errorcode AS [Error Code]
   , dsl.errordescription AS [Error Description]
FROM 
   dbo.sysdtspackagelog dpl
   INNER JOIN dbo.sysdtssteplog dsl
      ON dpl.lineagefull = dsl.lineagefull
WHERE
   dpl.logdate = (SELECT
                     MAX(dpl1.logdate)
                  FROM
                     dbo.sysdtspackagelog dpl1
                  WHERE
                     dpl1.name = dpl.name)
   AND dpl.name = @package_name
ORDER BY
   dpl.starttime

Rate

Share

Share

Rate