Legacy DTS package logs under SQL 2005

Please start any new threads on our new site at https://forums.sqlteam.com. 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?

CODO ERGO SUM

Qualis
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:
http://weblogs.sqlteam.com/jamesn/archive/2008/02/11/60502.aspx
http://weblogs.sqlteam.com/jamesn/archive/2008/02/11/60504.aspx
http://weblogs.sqlteam.com/jamesn/archive/2008/05/01/60583.aspx

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 
	pklog.name		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
from
	msdb.dbo.sysdtssteplog stlog
	left join
	msdb.dbo.sysdtspackagelog pklog
	on stlog.lineagefull = pklog.lineagefull
where
	stlog.errorcode <> 0
	and stlog.starttime > DATEADD (day, -15, getdate())
order by
	stlog.starttime

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources