Monday, July 27, 2015

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

Since, going back through my old post (, 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.

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

CREATE NONCLUSTERED INDEX ncidx__internal_event_messages__event_message_id
ON [internal].[event_messages] ([package_name])
INCLUDE ([event_message_id])

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! 

No comments: