Views for SSIS Packages

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SSIS_Perfomance]
AS
SELECT
 executions.execution_id
,executions.folder_name
,executions.project_name
,executions.package_name
,executables.package_path
,executable_statistics.execution_path
,executables.executable_name
,executions.executed_as_name
,executable_statistics.start_time
,executable_statistics.end_time
,CONVERT(date, executable_statistics.end_time) AS endDate
,executable_statistics.execution_duration
,CASE executable_statistics.execution_result
WHEN 0 THEN 'Success'
WHEN 1 THEN 'Failure'
WHEN 2 THEN 'Completion'
WHEN 3 THEN 'Cancelled'
END AS result
,CASE operations.status
WHEN 1 THEN 'Created'
WHEN 2 THEN 'Running'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'Failed'
WHEN 5 THEN 'Pending'
WHEN 6 THEN 'Ended Unexpectedly'
WHEN 7 THEN 'Succeeded'
WHEN 8 THEN 'Stopping'
ELSE 'Completed' END AS Status
,executable_statistics.statistics_id
,executable_statistics.executable_id
FROM
SSISDB.internal.executions AS executions
INNER JOIN SSISDB.internal.executable_statistics AS executable_statistics
ON executable_statistics.execution_id = executions.execution_id
INNER JOIN SSISDB.internal.executables AS executables
ON executables.executable_id = executable_statistics.executable_id
INNER JOIN SSISDB.internal.operations AS operations
ON operations.operation_id = executions.execution_id
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SSIS_Errors]
AS
SELECT
TOP (100) PERCENT operation_messages.operation_id
,operation_messages.message_time
,operation_messages.message
,CASE operation_messages.message_source_type
WHEN 10 THEN 'Entry APIs, such as T-SQL and CLR Stored procedures'
WHEN 20 THEN 'External process used to run package (ISServerExec.exe)'
WHEN 30 THEN 'Package-level objects'
WHEN 40 THEN 'Control Flow tasks'
WHEN 50 THEN 'Control Flow containers'
WHEN 60 THEN 'Data Flow task' END AS Status
FROM
SSISDB.internal.operations AS operations
INNER JOIN SSISDB.internal.operation_messages AS operation_messages
ON operation_messages.operation_id = operations.operation_id
WHERE
operations.status = 4
AND operation_messages.message_type IN (120, 130)
ORDER BY
operation_messages.message_time DESC
GO

select
	executable_name,
	start_time,
	end_time,
	execution_duration/60/1000 as duration_min
from [MyDB].dbo.[SSIS_Perfomance] m1
where m1.endDate>='2021-12-25'
and m1.package_name='Package.dtsx'
and package_path<>'\Package'
and executable_name like 'SQL%'
order by 1, 2

SELECT
E.project_name as EXECUTION_AREA
 ,E.package_name AS EXECUTION_PACKAGE_NAME
 ,E.start_time AS EXECUTION_START_TIME
 ,CASE (E.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 EXECUTION_STATUS
FROM SSISDB.catalog.executions E WITH (NOLOCK)
where E.start_time >= DATEADD(DD, -41, GETDATE())

select *
FROM (
select
executable_name,
start_time,
end_time,
execution_duration/60/1000 as duration_min
,execution_duration/60/1000 - (LAG(execution_duration) OVER (PARTITION BY executable_name ORDER BY start_time ))/60/1000 as diff
from [VT_VD].dbo.[SSIS_Perfomance] m1
where m1.endDate>='2021-09-01'
and m1.package_name='package.dtsx'
and package_path<>'\Package'
and executable_name like 'SQL%'
) T
where diff >= 10
order by 1,2