[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: