[SSIS 2012] Using SQL Sentry & custom code to send detailed email on failure

(update 2013/07/31 dealing with "cancelled")

This is a simple modification of http://thebakingdba.blogspot.com/2012/11/sql-server-2012-ssis-getting-useful.html, which sends an email with specific job failure info, rather than the nondescript "To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report ", which is pretty freaking useless.

We use SQL Sentry, which makes this part simple (they have their own method of logging SSIS failures, but it has several prereqs I don't like - specifically, it changes the step from SSIS to CmdExec, and executes the parameter-laden string).  My original post/SP required you to add a job step to each job - this one gets set on the server.  Just add an SQL Sentry action ("SQL Server Job Agent: Failure") and add "Execute SQL", running the following code, and it takes care of every job on the server. 

exec yourdbname.dbo.ssis_job_failure_info_fromjobname @job_name = '<%ObjectName%>'

(where yourdbname is wherever you store the SP)

As a bonus, I use the SQL Sentry email SP, so it appears to come from SQL Sentry.  That could be useful for you, based on your filtering.

One note: if the job fails because of an SSIS timeout (see: http://connect.microsoft.com/SQLServer/feedback/details/725840/catalog-executions-getting-cancelled-without-logging-any-error), no email is sent. That's because, while the job failed, technically the package doesn't know it failed... or something like that.  Read the Connect item for more details.  Supposedly fixed in SP1, but haven't had it happen up to now. but if it happens to you, there are a handful of fixes available.

1. Add the 5 indexes as per http://www.ssistalk.com/2013/01/31/ssis-2012-catalog-indexing-recommendations/
2. Change Retention period as per: http://www.made2mentor.com/2013/02/setup-and-performance-issues-with-the-integration-services-ssis-2012-catalog/ 
1. Don't just set it to 90 if you have a lot of history! You need to iterate through days, running the maint job between each.
3. Change DB size settings as per same post
4. Ensure Database is in SIMPLE mode.
5. (possibly turn on snapshot isolation)
6. (I also turn on Async Statistics Update) 
7. http://support.microsoft.com/kb/2829948 - There is a fix from MS!   SP1 CU4 has a fix.  It says it just adds indexes; it must change the delete SP, since adding the indexes did NOT fix it for me.

And here's the modified SP:

	/*
	SQL Server Agent Job: Failure
	Execute SQL:
	exec yourdbname.dbo.ssis_job_failure_info_fromjobname @job_name = '<%ObjectName%>'
	If performance is lacking, add the following indexes to SSISDB, as these tables
	do cascading deletes and are FKs and also aren't indexed. Index script found
	on a MS connect item
	CREATE NONCLUSTERED INDEX [NCIX_operation_id]
	ON [internal].[event_messages] ([operation_id]);
	GO
	CREATE NONCLUSTERED INDEX [NCIX_operation_id]
	ON [internal].[operation_messages] ([operation_id]);
	*/
	/****** Object: StoredProcedure [dbo].[ssis_job_failure_info_fromjobname] Script Date: 5/2/2013 8:12:30 AM ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE PROCEDURE [dbo].[ssis_job_failure_info_fromjobname] @job_name VARCHAR(500)
	AS
	DECLARE @full_ssis_command VARCHAR(4000),
	@job_step_id INT,
	@package_name NVARCHAR(520),
	@tableHTML NVARCHAR(MAX),
	@MailSubject VARCHAR(200),
	@job_id UNIQUEIDENTIFIER,
	@job_description NVARCHAR(1024),
	@job_category SYSNAME
	SET NOCOUNT ON
	--2013/05/06 first released version. thebakingdba.blogspot.com.
	--No token replacement; grabs from SQL Sentry which passes the job name.
	--Get all the other relevant details - description, category
	--2013/05/08 mdb changed package_name to nvarchar(520), as per the system table.
	-- Also removing the extraneous call to table, and lengthening message to 500.
	SELECT @job_id = job_id,
	@job_description = sysjobs.[description],
	@job_category = syscategories.name
	FROM msdb.dbo.sysjobs
	INNER JOIN msdb.dbo.syscategories
	ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
	WHERE sysjobs.name = @job_name
	--determine which job_step failed.
	SELECT TOP 1
	@job_step_id = step_id
	FROM msdb.dbo.sysjobhistory
	WHERE run_status <> 1
	AND step_id > 0
	AND job_id = @job_id
	ORDER BY instance_id DESC
	--now find the package name
	SELECT @full_ssis_command = command
	FROM msdb.dbo.sysjobsteps
	WHERE job_id = @job_id
	AND step_id = @job_step_id
	IF @full_ssis_command LIKE '%.dtsx%'
	BEGIN
	SELECT @package_name = RIGHT(LEFT(@full_ssis_command,
	CHARINDEX('.dtsx',
	@full_ssis_command) - 1),
	CHARINDEX('\',
	REVERSE(LEFT(@full_ssis_command,
	CHARINDEX('.dtsx',
	@full_ssis_command)
	- 1))) - 1)
	+ '.dtsx'
	END
	IF @full_ssis_command LIKE '%.dtsx%'
	BEGIN
	--goes in the error log, if you have one
	SELECT [message_time],
	[extended_info_id],
	[package_name],
	[message_source_name],
	[subcomponent_name],
	[package_path],
	[execution_path],
	LEFT([message], 400)
	FROM ssisdb.[catalog].[event_messages]
	WHERE [package_name] = @package_name
	AND event_name = 'OnError'
	AND operation_id IN (
	SELECT MAX(operation_id)
	FROM ssisdb.[catalog].[event_messages]
	WHERE [package_name] = @package_name)
	ORDER BY message_time ASC
	SELECT @MailSubject = 'Job Failure on ' + @@servername + ': '
	+ @job_name
	SET @tableHTML =
	'<html><body>Job Description: ' + ISNULL(@job_description, N'') + N'<br>'
	+ N'Job Category:' + ISNULL(@job_category, N'') + N'<br><br><br><hr>'
	+ N'<H3>Error for job ' + @job_name + '</H3>'
	+ N'<table border="1">' + N'<th>Message_Time</th>'
	+ N'<th>Extended_info_id</th>' + N'<th>Package_Name</th>'
	+ N'<th>Message_Source_Name</th>' + N'<th>subcomponent_name</th>'
	+ N'<th>package_path</th>' + N'<th>execution_path</th>'
	+ N'<th>message</th>'
	+ CAST((SELECT td = CONVERT(VARCHAR(20), message_time, 120),
	'',
	td = CONVERT(VARCHAR(10), ISNULL(extended_info_id,
	'')),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL(package_name,
	'')))),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([message_source_name],
	'')))),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([subcomponent_name],
	'')))),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([package_path],
	'')))),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([execution_path],
	'')))),
	'',
	td = CONVERT(VARCHAR(400), RTRIM(LTRIM(LEFT(ISNULL([message],
	''), 400))))
	FROM ssisdb.[catalog].[event_messages]
	WHERE [package_name] = @package_name
	AND event_name = 'OnError'
	AND operation_id IN (
	SELECT MAX(operation_id)
	FROM ssisdb.[catalog].[event_messages]
	WHERE [package_name] = @package_name)
	FOR
	XML PATH('tr'),
	TYPE
	) AS NVARCHAR(MAX)) + N'</table></body></html>';
	--PRINT @tableHTML
	IF @tableHTML IS NOT NULL
	BEGIN
	EXEC msdb.dbo.sp_sentry_dbmail_20 @recipients = 'dev@null.com',
	@subject = @MailSubject, @body = @tableHTML,
	@body_format = 'HTML';
	END
	END
	GO

view raw gistfile1.txt hosted with ❤ by GitHub