Logging and Reporting Patterns - SQL Server Integration Services Design Patterns - page 386

select top 5 e.execution_id, e.package_name, DATEDIFF(ms,
start_time, end_time) as duration_ms
from catalog.executions e
where e.start_time > DATEADD(dd, -1, getdate())
order by duration_ms desc
The second reason you may want a new report is to see why a package failed. You
will use an additional view for this information, the catalog.event_messages
view. Restricting the data on both the executions and the event_messages
view will ensure that you get only packages that failed entirely and see only the events
that caused them to fail. This query can be seen in Listing 15-5 .
Listing 15-5 . Failed-Packages Query
select e.execution_id, e.package_name, em.*
from catalog.executions e
inner join catalog.event_messages em on
e.execution_id=em.operation_id
where e.status = 4 and em.event_name = 'OnError'
The final reason is to understand the inner workings of a particular component. You
can see the individual steps that occurred during the execution of each component in
the data flow. For example, the query in Listing 15-6 returns each step that occurs in
the execution of the sources, transformations, and destinations and how long each step
takes.
Listing 15-6 . Query to Return Component Phases and Times
select subcomponent_name, phase
, DATEDIFF(ms, start_time, end_time) as
duration_ms
from catalog.execution_component_phases
where package_name = 'Ch16_Reporting.dtsx'
and task_name = 'Data Flow Task'
Once you have your desired query, you can either run it directly from Management
Studio or embed it into a Reporting Services report to make it look like the standard re-
ports available in the solution. To make the report through Management Studio, you