April 23, 2009 at 1:06 pm
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 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?
April 23, 2009 at 2:12 pm
Are sure you have added logging to the SSIS package. By deafult there is no logging, you have to define it , with connection string to msdb, or some ohter database if you prefer. It is sysdtslog90 table.
-VG
April 23, 2009 at 3:29 pm
VG (4/23/2009)
Are sure you have added logging to the SSIS package. By deafult there is no logging, you have to define it , with connection string to msdb, or some ohter database if you prefer. It is sysdtslog90 table.-VG
He's talking about legacy DTS packages rather than SSIS packages, but it's still a good idea to check the package and make sure logging is enabled.
Michael, my DTS packages do write to the sysdtspackagelog table in msdb if you set the package to log to SQL Server. We use this query to select step errors:
select
pklog.name
, stlog.stepname
, stlog.stepexecutionid
, stlog.starttime
, stlog.endtime
, stlog.errorcode
, stlog.errordescription
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 starttime
You could also set the packages to log to a file. I think that's a little easier to read.
Greg
April 23, 2009 at 3:56 pm
Greg Charles (4/23/2009)
VG (4/23/2009)
Are sure you have added logging to the SSIS package. By deafult there is no logging, you have to define it , with connection string to msdb, or some ohter database if you prefer. It is sysdtslog90 table.-VG
He's talking about legacy DTS packages rather than SSIS packages, but it's still a good idea to check the package and make sure logging is enabled.
Michael, my DTS packages do write to the sysdtspackagelog table in msdb if you set the package to log to SQL Server. We use this query to select step errors:
selectpklog.name
, stlog.stepname
, stlog.stepexecutionid
, stlog.starttime
, stlog.endtime
, stlog.errorcode
, stlog.errordescription
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 starttime
You could also set the packages to log to a file. I think that's a little easier to read.
Greg,
I guess you are saying that there is no way to view the logs in SSMS, so you just have to use a query?
April 24, 2009 at 9:39 am
Yes. It's a hassle, compared to using Enterprise Manager in 2000, but it's all they've given us.
Greg
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply