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

select * from msdb.dbo.sysssislog
This statement returns results similar to those in Figure 15-2 .
Figure 15-2 . Results from the SSIS log table
Design Pattern: Package Executions
Although it is possible to use the information in the table directly, you can also com-
bine the information to make it a little more readable. If you want to see the package
executions and how long each page took to run, you can use the query in Listing 15-1 :
Listing 15-1 . Query to Return Package Durations
select ssis.source
, min(starttime) as package_start
, max(endtime) as package_end
,DATEDIFF(ms, min(starttime), max(endtime)) as
duration_ms
from msdb.dbo.sysssislog ssis
where event in ('PackageStart', 'PackageEnd')
group by ssis.source, ssis.executionid
Catalog Logging and Reporting
The catalog logging and reporting method was introduced in Integration Services 2012
and is the best logging method to use if it is available. It can be used only if you have
set up the project deployment model type. The nice thing about this type of logging is