Technical Article

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 ByChange(s)
| =============== ============== ===================================================
DECLARE @package_name sysname

SET @package_name = 'dnc_extr_new_dnc_accounts'

   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]
   dbo.sysdtspackagelog dpl
   INNER JOIN dbo.sysdtssteplog dsl
      ON dpl.lineagefull = dsl.lineagefull
   dpl.logdate = (SELECT
                     dbo.sysdtspackagelog dpl1
   AND = @package_name