SQL Server Performance Forum – Threads Archive

Accessing DTS Package Logs

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)

---

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

---

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.

---

]]>