Hi All,

I want to create a log file for job failure information like jobname,Stepname,Errordescription,Runstatus,startdate and End Date.

I want to create this file(.txt) automatically in the location, if the job fails.

How can we achive this...

Thanks in Advance.

You can do this using OnError event handler.

You can use system variables available within SSIS for capturing the required info

http://visakhm.blogspot.in/2013/03/error-handling-in-ssis-loops.html

Thanks Visakh,

I want to create text file for failed jobs with jobname,Stepname,Errordescription,Runstatus,startdate and End Date information in Sql server Agent job level. How can we achive this.

Thanks in Advance...

1) create trigger which store job failure information

2) you can mail or keep data in table also use you logic.

3) followinf just format to understand logic.

Create  trigger [dbo].[trg_stepfailures]
on [dbo].[sysjobhistory]
for insert
as
declare @strMsg varchar(400),
if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')
begin
select @strMsg = 
convert(char(10),'Server') + char(58) + @@servername +
char(10) +
convert(char(10),'Job') + char(58) + convert(varchar(50), sysjobs.name) + 
char(10) +
convert(char(10),'Step') + char(58) + convert(varchar(50), inserted.step_name)+
char(10) +
convert(char(10),'Message') + char(58) + convert(varchar(150), inserted.message)
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0
end 

3) check and confirm.

• Proposed as answer by Dinesh Vishe 1 hour 23 minutes ago

The Advanced tab of the SQL Agent job step will allow you to log to an output file.  You can then use tokens to dynamically create the filename

• Proposed as answer by Martin.CairneyMVP 45 minutes ago