Peter Schott improves upon Kevin Hill’s script:
Recently, Kevin Hill (b | t ) posted on getting package errors from the SSIS catalog in a single query as opposed to clicking through the SSIS Reports and digging through pages. I took that and ran with it a little bit. The first pass needed an additional index on the catalog to increase performance. Kevin’s included that at the bottom of his query on the post above. (You probably don’t need the included “message” column, though.)
I wanted to take this and run with it a little bit to report on all errors for a given folder within the last day, then e-mail that in an HTML formatted e-mail. To that end, I wrote up a quick stored procedure that should take the Folder or Package or Project name and a “to” e-mail address to send an e-mail through DBMail.
Click through for the script.