3

I have 3 SQL Agent jobs that execute SSIS packages. When the job errors out, it generates an email that has 'The job failed - ' in subject and in the email has a message 'The job failed. The job was invoked by schedule schename, the last step to run was Run_'

It doesn't have the FULL error message that is visible when I view history of the job. Is there any way that the job can be configured to include the COMPLETE error message in the email? Or, is there a way that I can build a stored procedure which will be executed upon a specific job failure, and that queries one of the system tables (sysjobs)?

I've never done it before but I guess I can build a trigger into the sysjobs table that is triggered once a specific job fails, and does a sp_db_sendmail. I'm trying to avoid going this route. Is there an optimal way of accomplishing my task?

Roger Dodger
  • 927
  • 2
  • 16
  • 37
  • Here's a sample query for extracting messages from the ssisdb catalog. These provide much more info than can be found in any job logs. You (or I) can extend this to return just the latest errors for a given package name. http://sqlblog.com/blogs/jamie_thomson/archive/2012/10/17/querying-the-ssis-catalog-here-s-a-handy-query.aspx – Nick.Mc Oct 17 '16 at 21:26
  • @Nice.McDermaid - I've got something similar but I need something that is generated as soon as the job fails - not a scheduled job that runs at a set time and gets all the info for the failed jobs. In other words, as soon as the job fails, and the notification email is sent, I need all the error message info in that email, or, another email that I can build to capture the entire message. – Roger Dodger Oct 18 '16 at 09:02
  • Well you already have something sending emails - just add it to that. If you describe your process for sending emails we can see if we can work something out. Is it just a SQL Agent job step on failure or is it inside the SSIS package? – Nick.Mc Oct 18 '16 at 10:01
  • @Nick.McDermaid - The current email is set in the 'Notifications' when the job fails. This is set in the Job, there is nothing set in the ssis package. The job just executes the ssis package. – Roger Dodger Oct 18 '16 at 11:13
  • With regards to only sending after failure (not polling), follow the advice of @Pinwar13. But you need query the SSIS catalog, not the SQL Agent tables. As you surmised, the agent tables are no use for many reasons. Just query the SSIS catalog tables instead. The only trick is you'll need to search only on the correct package identifier to get the correct logs for the package – Nick.Mc Oct 18 '16 at 11:18
  • @Nick.McDermaid - I see only a few sysssis tables in msdb. What are the specific tables I should be looking for? – Roger Dodger Oct 18 '16 at 17:36
  • Assuming this is SSIS 2012, SQL Agent no longer provides useful SSIS package messages so forget about msdb. Instead you need to look in the integration services catalog, which someone would have needed to configure at some point. Mine is called SSISDB. The first link I posted shows that tables in here that you need to look at and some sample queries. Are you using the SSIS catalog? Are you using package deployment model or project deployment model? – Nick.Mc Oct 18 '16 at 21:29
  • I notice you are in the habit of not marking questions as answered – Nick.Mc Oct 18 '16 at 21:31

2 Answers2

0

One better way is to use multiple job steps.

Step1: Execute Package

If Step1 fails:

Step2: use T-SQL with sp_db_sendmail

Step2 can be customized in many ways like ou can attach the log generated by ssis package of previous step.

Or

Get the Error message from Job history and add to sp_db_sendmail Refer.

If you are using Project deployment model, you can do a lot with catalog like executing ssis package using SP, getting error detail from log tables.

p2k
  • 2,126
  • 4
  • 23
  • 39
  • Using your method, if I have a step 2 that queries the sysjobs and sysjobhistory - would the error info that just occurred be already logged in them? Or is that error info logged into those tables AFTER the entire job has finished, including Step 2? If so, it wouldn't help my purpose, since there will be no error info that just occurred in Step 1. Am I wrong? – Roger Dodger Oct 18 '16 at 09:12
0

Here is a script that you can use in a job. It looks for any jobs that have failed on the current day. You can run this once a day to see what jobs failed on that day. You can add a time variable if you want to run this every 30 minutes or so and only see the jobs which failed within the last hour. You have to be able to use xp_cmdshell which i assumed you would be able to since you want to use sp_send_dbmail

declare @filepath nvarchar(100)     --file path where results are stored as csv
declare @filename nvarchar(100)     --file name where results are stored
declare @command nvarchar(4000)     --dynamic sql of bcp command
declare @count int                  --result count
declare @emailList varchar(4000)    --people to email
declare @dt int                     --current date in INT format



set @filepath = '"e:\somefolder\'
set @filename = 'Failures.csv"'
set @emailList = 'email@domain.com; someOtherEmail@domain.com'
set @dt = (select convert(int,replace(convert(date,substring(convert(varchar,getdate()),1,11)),'-','')))

--query to get the jobs that failed and why. Looks for jobs that were executed today.
select
    h.run_date,
    h.run_time,
    h.run_status,
    j.name,
    j.description,
    h.message
from 
    msdb.dbo.sysjobhistory  h
    inner join
    msdb.dbo.sysjobs j on j.job_id = h.job_id
where
    h.run_status = 0
    and h.run_date = @dt

--if there were failures then put them in a csv and email them.
set @count = @@ROWCOUNT

if (select @count) > 0
begin
    set @command = 'bcp "select h.run_date, h.run_status, j.name, j.description, h.message from msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j on j.job_id = h.job_id where h.run_status = 0 and h.run_date = convert(int,''' + convert(varchar,@dt) + ''') " queryout '
    set @command = @command + @filepath + @filename + ' -c -t, -T '----> character data, tab delimtted is default use  -t, for comma ... trusted conn,server instance to connect to
    exec master..xp_cmdshell @command

    exec msdb.dbo.sp_send_dbmail 
                    @profile_name = null,
                    @recipients  = @emailList,
                    @body = 'Attached is the job failure results',  
                    @body_format = 'TEXT',
                    @subject = 'Job Failures',
                    @file_attachments = 'e:\someFolder\Failures.csv'

end
S3S
  • 24,809
  • 5
  • 26
  • 45
  • Again, this is a scheduled job, what I need is something that is generated right when the job fails. The entire error message needs to be emailed as soon as the job fails. – Roger Dodger Oct 18 '16 at 09:03
  • So run this every minute. It's not taxing – S3S Oct 18 '16 at 11:11
  • I've been asked to come up with an optimal solution - setting a 1 min schedule wouldn't be one. – Roger Dodger Oct 18 '16 at 11:14
  • Well if the job is important then when it failed you'll be launching SSMS to investigate anyway and rerun it, so the point is mute from the start. Is this going to be ran on n number of jobs? If so you can use a trigger on the log table – S3S Oct 18 '16 at 11:42
  • there currently is an email sent upon job failure. The rub is that the error message is not fully included in email. Trigger is one option, if the other one suggested by pinwar and Nick.McDermaid doesn't work. – Roger Dodger Oct 18 '16 at 17:30
  • 1
    @DamonMatt That's because it isn't written to the table until the job reports the failure, which happens after the job ends so placing something in an additional step in the job isn't going to give you the error message since it isn't there yet. – S3S Oct 18 '16 at 18:07