SSIS 2012 has (arguably) two main improvements over prior versions. The first is a project deployment model where an entire project can be deployed and managed with a common structure. The second is a more favourable logging mechanism. This post looks at the latter and examines reporting on package and job execution in SSIS 2012.
There are standard inbuilt reports that show the execution of the project (and individual packages within the project). These can be seen in SQL Server Management Studio (SSMS) by right clicking on the SSIS catalogue (or node) and selecting the “All Executions” report from the reports menu (as below). Note that although you can get to the report through the path Reports > All Executions or Reports > Standard Reports > All Executions, the reports are the same (and shown below).
|Accessing the Execution Reports through SSMS|
|Sample Report Output|
One of the really great features of this report is that you can click on the Overview hyperlink and see what packages have executed as part of the (node) execution. So, for example, the packages that have executed under my ‘Catalog Testing’ (project) execution are listed since this is the node I selected (when I selected overview from the ‘All Executions’ report above. An example of this output is seen below. You may note that the overview includes the tasks that have executed within the package.
|Overview Report (accessed by the Overview link in the ‘All Executions’ report)|
But Wait – There’s More!
While these reports are good, they require SSMS to view the reports, so you might want to interrogate the SSIS database directly. Using this you can create some custom reports and expose these as normal SSRS reports.
Two views that are invaluable for examining package executions (and task executions) are catalog.executions
and catalog.executable_statistics. From MSDN the executions view shows “instances package execution in the Integration Services Catalogue”, therefore, we can use this to show (and identify the execution_id) of packages when execute. Consider the “All Executions” report above which shows the execution_id of 10275. This can be easily identified in the executions view with the query;
where execution_id = 10275
More importantly we can easily identify executions of packages (or groups of packages) by referring to the package_name field in this table.
Once we have the execution_id, we can then use this in the statistics view to see the detailed execution of the package. This is the same data that is shown in the ‘Overview’ report above.
where execution_id = 10275
Most of my projects utilise a parent/child architecture, so when I consider the natural drill path for package executions as the master_package à child_package à tasks. If I want to only show the packages that have executed as part of the Master package (note Master_1.dtsx package name in the executions above) I can simply use the following query;
, CONVERT(datetime, es.start_time) AS start_time
, CONVERT(datetime, es.end_time) AS end_time
, es.execution_duration , es.statistics_id
, case es.execution_result
when 0 then ‘Success’
when 1 then ‘Failure’
when 2 then ‘Completion’
when 3 then ‘Cancelled’
end as execution_result_description
from catalog.executables e
join catalog.executable_statistics es
on e.executable_id = es.executable_id
and e.execution_id = es.execution_id
where package_path = ‘\Package’
and e.execution_id = 10275
A Word of Caution
while they SSISDB database can be used to provide some nice reporting, the executions view contain some inbuilt security which implements row security. If we examine the definition of the view we can see that the where clause includes the following conditions.
|WHERE opers.[operation_id] in (SELECT id FROM [internal].current_user_readable_operations])
OR (IS_MEMBER(‘ssis_admin’) = 1)
OR (IS_SRVROLEMEMBER(‘sysadmin’) = 1)
The credential for accessing the database should be a member of ssis_amdin or sysadmin role. If not, no rows are returned.
I will leave it to your own devices to come up with alternate methods for bypassing this requirement.