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. I’m assuming a profile set up for “Default” within DBMail that can be used. If not, feel free to change that in the code.
This is defined just to pick up all errors within the last 24 hours and not trying to be incredibly robust to work with combinations of folder/project/path. All of our needs are for the Folder level so I haven’t tried to enhance this to work with combinations of the names. Code for the stored procedure is below: