note that the procedure was formerly named sp
ssicatalog and hence you may still see that name in some of the screenshots. _
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]).
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.
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):
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'
usp_ssiscatalog can also return information about all executions:
EXEC [dbo].[usp_ssiscatalog] @action='execs'
The most recent execution will appear at the top.
usp_ssiscatalog provides a number of parameters that enable you to filter the resultset:
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'