THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

sp_ssiscatalog v1.0.4.0 available now with new feature - view most recent failed execution [SSIS]

Development of sp_ssiscatalog continues apace with a new release today (4th July 2013). In this release I have enabled a new action called ‘exec_failed’ which can be simply described as “Show me the most recent failed execution”.

The syntax is very simple:

exec sp_ssiscatalog @action = 'exec_failed'
exec sp_ssiscatalog @a = 'exec_failed' /*shortened form*/

That command will dig into the SSIS catalog to find the most recent failed execution and display the same information that would be displayed, by default, for any execution


You can use the existing parameters @exec_warnings, @exec_errors, @exec_events, @exec_executable_stats, @exec_events_packages_excluded to modify the resultsets that are returned by default and hence if you want to simply view only the errors its

exec sp_ssiscatalog


The main aim here was to provide easy access to information about the most recent failed execution but I implemented it in such a way that its easy to return information on any execution status hence all of the following can be used:

exec sp_ssiscatalog @action = 'exec_created'
exec sp_ssiscatalog @action = 'exec_running'
exec sp_ssiscatalog @action = 'exec_canceled'
exec sp_ssiscatalog @action = 'exec_failed'
exec sp_ssiscatalog @action = 'exec_pending'
exec sp_ssiscatalog @action = 'exec_ended_unexpectedly'
exec sp_ssiscatalog @action = 'exec_succeeded'
exec sp_ssiscatalog @action = 'exec_stopping'
exec sp_ssiscatalog @action = 'exec_completed'

Download the goods from here. Steps to install are at Installation Instructions.

Its worth pointing out that this feature was implemented solely because it was requested by someone going by the name mbourgon. He/she let a comment on my blog post Introducing sp_ssiscatalog v1.0.0.0, I thought it was a great idea so I put it in. if you have an idea for something you’d like to see in sp_ssiscatalog then let me know, I might just put that in too!


Published Thursday, July 4, 2013 10:19 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



mbourgon said:

Thanks, Jamie!  Mucking with it now.

July 18, 2013 4:46 PM

Sam said:

Jamie! This is awesome.  Thank you so much for this.  I was wondering if there was a way to query the output with a date filter, and write to an output file? I need this information so that the scheduling agent can read the output from the file, and make a decision to execute the rest of the jobs.  Appreciate your help.

September 4, 2015 2:40 PM

jamiet said:

Hi Sam,

No, no way to do that directly. You could change the code or insert the output into a table and filter on that.

September 4, 2015 3:27 PM

Leave a Comment


This Blog


Privacy Statement