[SSIS 2012] SQL Sentry - using custom code to send detailed SSISDB failure emails (part 2)

Since, going back through my old post (http://thebakingdba.blogspot.com/2013/05/ssis-2012-using-sql-sentry-custom-code.html), I found that it was hard to figure out where you add the condition to SQL Sentry event manager. I need to add this to SQLSentry's just-announced condition repository.

Step 1: grab code from below, and put it in a database of your choosing.
http://thebakingdba.blogspot.com/2013/05/ssis-2012-using-sql-sentry-custom-code.html

Step 2: index!  You probably need something like this index.  Mind the blocking during creation

USE [SSISDB]

GO

CREATE NONCLUSTERED INDEX ncidx__internal_event_messages__event_message_id

ON [internal].[event_messages] ([package_name])

INCLUDE ([event_message_id])

GO

Step 3: open SQL Sentry, go to the SSISDB computer in your list of servers, then expand.  Click on the main subgroup, which has the DB icon, the server name , the SQL Server Version, then "(EM/PA)" if you're running both.

Step 4: expand "Conditions".  View->Conditions, or it's already a tab.

Step 5: Under "Explicit", there's an "Add" button.  Click

Step 6: In "Actions Selector: General Conditions", on the right side of the window find "SQL Server Agent Job", then "SQL Server Agent Job: Failure", then select "Execute SQL".

Step 7: Under server, choose the same server (you could run the code on a different box, but the data we need is on that box).

Step 8: For T-SQL Command Text, enter (I use a different name than in the original post, since I use both variants, depending on the server)

exec dba_utils.dbo.ssis_job_failure_info_fromjobname_SqlSentry @job_name = '<%ObjectName%>'

Step 9: Click in some other server or window, so that the details are saved. 





Step 10: Test!