Accessing DTS Package Logs | SQL Server Performance Forums

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.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |