Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Legacy DTS package logs under SQL 2005

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-04-22 : 15:52:01
When you run a DTS package in SQL 2000, package execution info is logged to tables msdb.dbo.sysdtspackagelog and msdb.dbo.sysdtssteplog.

The results in the results can be viewed in the SQL Server 2000 Enterprise Manager by selecting a package, right-clicking on it, and selecting "Package Logs..."

These tables are present in SQL Server 2005, but it looks like legacy DTS packages do not populate them, at least by default, and SSMS does not have a feature to look at the logs.

Does anyone know if the DTS log info goes someplace different in 2005, or if there is a way to force packages to populate these tables? Also, is there a 2005 tool for looking at the package log info?


Posting Yak Master

145 Posts

Posted - 2009-04-27 : 14:37:17
The logs go into sysdtslog90 in the database you specify when configuring logging. You cannot log the same way DTS logs, but the SSIS logging is pretty extensive. You can see my blog posts at:
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-04-27 : 17:52:46
I was not asking about logging in SSIS, I was asking about logging in Legacy DTS packages running under SQL Server 2005.

I was able to find out what was going on. Legacy DTS packages are still logged to the same tables as under SQL 2000, msdb.dbo.sysdtspackagelog and msdb.dbo.sysdtssteplog, provided you have package logging to SQL Server enabled in the DTS package properties.

2005 SQL Server Management Studio does not provide the ability to look at Legacy DTS package logs, so you have to use a query to look at the logs.

Example query, modify to suit your needs:

select as PackageName ,
stlog.stepname as StepName ,
stlog.stepexecutionid as StepExecutionID ,
stlog.starttime as StepStartTime ,
stlog.endtime as StepEndTime ,
stlog.errorcode as StepErrorCode ,
stlog.errordescription as StepErrorDescription
msdb.dbo.sysdtssteplog stlog
left join
msdb.dbo.sysdtspackagelog pklog
on stlog.lineagefull = pklog.lineagefull
stlog.errorcode <> 0
and stlog.starttime > DATEADD (day, -15, getdate())
order by

Go to Top of Page

- Advertisement -