SSIS Database Queries

#

Prior to SQL 2012 I always monitored SSIS executions using dbo.sysssislog. Running a query directly against the SQL table allowed me to customise the information I receive back and change it on an ad-hoc basis. I use the following query to quickly retrieve data on SSIS executions.

 

USE [SSISDB] -- SELECT @@SERVERNAME

GO

DECLARE @package_name AS VARCHAR(255) = 'all' -- Default: 'All'

DECLARE @event_name AS VARCHAR(255) = 'all' -- Default: 'All'

DECLARE @execution_id AS INT = 0 -- Default: 0

/*

1.

[catalog].[executions] Displays information on instances of package execution in the Integration Services catalog. Filter package_name

*/

SELECT TOP 100

                 CONVERT(VARCHAR(19),[start_time], 120) AS StartTime,

                 CONVERT(VARCHAR(19),[end_time], 120) AS EndTime,

        CASE [status] WHEN 1 THEN 'created'

             WHEN 2 THEN 'running'

             WHEN 3 THEN 'canceled'

             WHEN 4 THEN 'failed'

             WHEN 5 THEN 'pending'

             WHEN 6 THEN 'ended unexpectedly'

             WHEN 7 THEN 'succeeded'

             WHEN 8 THEN 'stopping'

             WHEN 9 THEN 'completed'

        END AS [status_text] ,

                 FORMAT(DATEDIFF(minute, start_time, ISNULL(end_time, GETDATE()))/60,'00') + ':' +

        FORMAT(DATEDIFF(minute, start_time, ISNULL(end_time, GETDATE()))% 60,'00') + ':' +

        FORMAT(DATEDIFF(ss, start_time, ISNULL(end_time, GETDATE())) % 60,'00') AS [Duration(HH:MM:SS)] ,

        project_name AS ProjectName ,

        Package_Name AS PackageName ,

        executed_as_name AS ExecutedAsName,

                 Server_name AS ServerName,

                 machine_name AS MachineName,

                 total_physical_memory_kb AS [TotalPhysicalMemory(kb)],

                 available_physical_memory_kb AS [AvailablePhysicalMemory(kb)],

                 cpu_count AS CPUCount,

        total_page_file_kb as [TotalPageFile(kb)],

        available_page_file_kb as [AvailablePageFile(kb)],

        process_id as ProcessId,

                 [execution_id]

FROM    [catalog].[executions]

WHERE   (package_name = @package_name OR @package_name = 'ALL')

                 --AND CONVERT(VARCHAR(19),[start_time], 120) > DATEADD(DAY,-1, getdate())

ORDER BY start_time DESC

 

/*

           2.

event_messages : It performs a very similar role to [msdb]..[sysssislog]. Filter: event_name, package_name

*/

SELECT TOP 100

                 CAST(message_time AS DATETIME) AS message_time,

        message ,

        package_name ,

        event_name ,

        message_source_name

FROM    [catalog].[event_messages]

WHERE (event_name = @event_name OR @event_name = 'ALL')

         and (package_name = @package_name OR @package_name = 'ALL')

ORDER BY CAST(message_time AS DATETIME) DESC

 

/*

           3.

executable_statistics : Display component statistics, whether it succeeded or not and how long it took. Filter: execution_id

*/

SELECT  * FROM    [catalog].[executable_statistics] WHERE   (execution_id = @execution_id OR @execution_id = 0)

 

/*

         4.

  execution_parameter_values : Display execution parameters. Filter: execution_id

*/

SELECT  * FROM    [catalog].[execution_parameter_values] epv WHERE   (execution_id = @execution_id OR @execution_id = 0)

 

/*

         5.

  executables : Every single executable that gets executed in an execution

*/

SELECT  executable_id ,  executable_name ,  package_name ,   package_path FROM    [catalog].executables

 

I use this query because it allows me to customise and filter data returned from the SSISDB database. It also means I can compare executions and components to check if the time taken to execute them is increasing. This can be useful when performance tuning package executions.

 

More details on SSIS logging tables available on Jamie Thomsons blog here

http://sqlblog.com/blogs/jamie_thomson/archive/2011/07/16/ssis-logging-in-denali.aspx