Latest DTS Package Log

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'

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