THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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

image

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
                   @action=
'exec_failed',
                  
@exec_warnings=0
                  
@exec_events=0,
                  
@exec_execution=0,
                  
@exec_executable_stats=
0

image

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!

@Jamiet

Published Thursday, July 04, 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

Comments

 

mbourgon said:

Thanks, Jamie!  Mucking with it now.

July 18, 2013 4:46 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement