note that the procedure was formerly named spssicatalog and hence you may still see that name in some of the screenshots. _

usp_ssiscatalog

usp_ssiscatalog is a stored procedure that provides a quick and easy way to get at the most pertinent information in the SSIS Catalog ([SSISDB]).

Installation Instructions

Usage Scenarios

Most Recent Execution

I find that the most frequent information that one needs to get from the SSIS Catalog is information pertaining to the most recent execution. Hence if you execute usp_ssiscatalog with no parameters, that is exactly what you will get.
EXEC [dbo].[usp_ssiscatalog]
sp_ssiscatalog_basic.png
This will return up to 5 resultsets:
  • EXECUTION - Summary information about the execution including status, start time & end time
  • EVENTS - All events that occurred during the execution
  • OnError,OnTaskFailed - All events where event_name is either OnError or OnTaskFailed
  • OnWarning - All events where event_name is OnWarning
  • EXECUTABLE_STATS - Duration and execution result of every executable in the execution
All 5 resultsets will be displayed if there is any data satisfying that resultset. In other words, if there are no (for example) OnWarning events then the OnWarning resultset will not be displayed.
The display of these 5 resultsets can be toggled respectively by these 5 optional parameters (all of which are of type BIT):
  • @exec_execution
  • @exec_events
  • @exec_errors
  • @exec_warnings
  • @exec_executable_stats

Any Execution

As just explained the default behaviour is to supply data for the most recent execution. If you wish to specify which execution the data should return data for simply supply the execution_id as a parameter:
EXEC [dbo].[usp_ssiscatalog] 6

Most recent failed execution

To get all information about the most recently failed execution
EXEC usp_ssiscatalog @action = 'exec_failed'

All Executions

usp_ssiscatalog can also return information about all executions:
EXEC [dbo].[usp_ssiscatalog] @action='execs'
sp_ssiscatalog_executions.png
The most recent execution will appear at the top.
usp_ssiscatalog provides a number of parameters that enable you to filter the resultset:
  • @execs_folder_name
  • @execs_project_name
  • @execs_package_name
  • @execs_executed_as_name
  • @execs_status_desc
Some typical usages might be:
Return all failed executions
EXEC [dbo].[usp_ssiscatalog] @action='execs',@execs_status_desc='failed'

Return all executions for a specified folder
EXEC [dbo].[usp_ssiscatalog] @action='execs',@execs_folder_name='My folder'

Return all executions of a specified package in a specified project:
EXEC [dbo].[usp_ssiscatalog] @action='execs',@execs_project_name='My project', @execs_package_name='Pkg.dtsx'

Last edited Sep 4, 2013 at 10:10 AM by jamiet, version 14

Comments

No comments yet.