Setting up an alert to catch a failure in a DTS package execution
-
Hi.
I am trying to set-up an alert that will fire when a DTS package fails and a error message appears in the Windows Event Log. I have no problem getting the error to appear in the event logm but cannot get the alert to catch it.
I am at my first glance into setting up an alert. Books on-line doesn't help much.
Can anybody help me into this?
thanks!!
-
You could use a trigger on sysjobhistory to check for a job failure (if the dts is executed from there). You can then trap the raiserror as an alert.
CREATE trigger trg_stepfailures
on sysjobhistory
for insert
as
declare @strcmd varchar(1000),@strRecipient varchar(500),@strMsg varchar(3000), @subject varchar(100)
if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')
begin
select @strMsg = @@servername + ' Job ' + sysjobs.name +char(13) + char(13) + 'Step ' +
inserted.step_name +char(13)+char(13)+ 'Message ' + inserted.message
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0
raiserror (@strMsg, 16, 10) with log
end
GO
Or you could put a trigger on the sysdtssteplog table, which would look for a failed step in a dts package.
CREATE trigger trg_dtsfailures
on sysdtssteplog
for insert
as
declare @strcmd varchar(1000),@strRecipient varchar(500),@strMsg varchar(3000), @subject varchar(100)
if exists (select * from inserted where errorcode != 0)
begin
select @strMsg = @@servername + ' DTS ' + d.name +char(13) + char(13) + 'Failed'
from inserted
join sysdtspackagelog d
on inserted.lineagefull = d.lineagefull
where inserted.errorcode != 0
raiserror (@strMsg, 16, 10) with log
end
GO
-
Thanks for the script.
I will try it, but is there a reason I can't trap the message already created in the event log? Is it because it is not present in the SQL error log?
Y
-
Can't get the trigger to work. I found in BOL.
Note Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables.
The sysdtssteplog is a system table. Can we still do the trigger?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply