SQL Server Performance

Accessing DTS Package Logs

Discussion in 'General DBA Questions' started by Dave Wells, Mar 13, 2003.

  1. Dave Wells New Member

    I need to have users run DTS packages from a web page. I've got this working by starting a job that runs the DTS (so that the webpage does no timeout out amongst other reasons).

    When the DTS finishes I'd like to access the log (using another step in the job) for the recently run package and store failure details in another table.

    There's nothing in BOL that I can find about the DTS tables and theres nothing in systbl.chm (the Microsoft system table map).

    Anyone got any experience with these tables and how they work? (or know of a link to some good info anywhere)
  2. jasper_smith New Member

    There are quite a few ways to get at this info using TSQL or VBScript and the DTS Package Object Model. For a TSQL approach you can use (where TEST is your package name)


    exec msdb..sp_enum_dtspackagelog 'TEST',1
    also to get further details you can use msdb..sysdtssteplog


    select sl.stepexecutionid, sl.stepname, sl.stepexecstatus, sl.starttime,
    sl.endtime, sl.elapsedtime, sl.errorcode, sl.errordescription
    from msdb..sysdtssteplog sl
    join msdb..sysdtspackagelog pl
    on sl.lineagefull=pl.lineagefull
    where pl.name = 'TEST'
    and pl.logdate = (select max(logdate) from msdb..sysdtspackagelog p where name = 'TEST')
    and sl.stepexecutionid >= 0
    order by sl.stepexecutionid
    For a VBScript approach you can use something like


    Set oSQL = CreateObject("DTS.Application")
    Set oDTS = oSQL.GetPackageSQLServer("JASXP","","",256)

    Set Logs = oDTS.EnumPackageLogRecords("TEST","True","","","")

    For each LogRecord in Logs

    WScript.Echo LogRecord.LogDate & VbCrLf &_
    LogRecord.Name & VbCrLf &_
    LogRecord.Description & VbCrLf &_
    LogRecord.ExecutionTime & VbCrLf &_
    LogRecord.ErrorCode & VbCrLf &_
    LogRecord.ErrorDescription & VbCrLf

    Next

    Set oDTS = Nothing
    Set oSQL = Nothing

    However, as you can see, all this information is already stored in tables so why do you want to duplicate this data ?


    HTH

    Jasper Smith
  3. Dave Wells New Member

    I've ended up with this code

    SELECTstepname,
    errordescription
    FROM(SELECTTOP 1
    lineagefull
    FROMsysdtspackagelog
    WHERE name = 'TestOneDTS'
    ORDER BY StartTime DESC) DTS
    INNER JOIN sysdtssteplog ON sysdtssteplog.lineagefull = DTS.lineagefull
    WHEREsysdtssteplog.errorcode <> 0

    I need to duplicate the data as my connection from the webpages won't have access to the system tables.

Share This Page