Last active
November 5, 2019 05:09
-
-
Save mbourgon/5502095 to your computer and use it in GitHub Desktop.
Sends email with useful info on SSIS 2012 job failure - uses SQL Sentry.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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 = '[email protected]', | |
| @subject = @MailSubject, @body = @tableHTML, | |
| @body_format = 'HTML'; | |
| END | |
| END | |
| GO | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment