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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Legacy DTS package logs under SQL 2005
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/22/2009 :  15:52:01  Show Profile  Reply with Quote
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

USA
145 Posts

Posted - 04/27/2009 :  14:37:17  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/27/2009 :  17:52:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000