I need a dashboard for my SQL Agent JOBS that run SSIS Packages

I hear you. Here is a script in T-SQL that will fetch all the jobs (including the ones that are currently running) and displays a dashboard for each step. It will also display the last message SSIS generated (last error message in case of failure, or last message in case of a successful run).

WITH
	LastSessionId
AS
	(
		SELECT
			session_id	= MAX(session_id), job_id
		FROM
			msdb.dbo.sysjobactivity
		GROUP BY
			job_id
	),
	LastSessionInformation
AS
	(
		SELECT
			sja.session_id,
			sja.job_id,
			sja.job_history_id,
			sja.start_execution_date,
			sja.last_executed_step_date,
			sja.stop_execution_date,
			sja.next_scheduled_run_date
		FROM
			msdb.dbo.sysjobactivity AS sja
			INNER JOIN
			LastSessionId AS lsi
			ON sja.job_id = lsi.job_id
			AND sja.session_id = lsi.session_id
	),
	SsisExecution
AS
	(
		SELECT
			exe.folder_name,
			exe.project_name,
			exe.package_name,
			exe.process_id,
			opr.status,
			opr.operation_id,
			exe.start_time,
			exe.execution_id
		FROM
			SSISDB.catalog.operations AS opr
			INNER JOIN
			SSISDB.catalog.executions AS exe
			ON opr.process_id = exe.process_id
			AND opr.status IN
					(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded
		WHERE
			opr.object_type = 20	-- package
	),
	LatestMessage
AS
	(
		SELECT
			last_message_id = MAX(msg.event_message_id), msg.message_type, msg.operation_id
		FROM
			SSISDB.catalog.event_messages AS msg
		GROUP BY
			msg.operation_id, msg.message_type
	)
SELECT
	job.job_id,
	sjh.instance_id,
	job = job.name,
	lsi.start_execution_date,
	last_executed_date = COALESCE(lsi.stop_execution_date, lsi.last_executed_step_date),
	lsi.next_scheduled_run_date,
	sjh.step_id,
	sjh.step_name,
	sjh.run_status,
	IIF(lsi.start_execution_date IS NOT NULL, COALESCE(sjh.message, '>>> RUNNING'), NULL),
	package = exe.folder_name + '\' + exe.project_name + '\' + exe.package_name,
	msg.message,
	msg.message_source_name,
	msg.execution_path,
	msg.package_path,
	details = 'SELECT * FROM SSISDB.catalog.event_messages WHERE operation_id = '
			+ CAST(exe.operation_id AS VARCHAR(50)) + ' ORDER BY message_time DESC'
FROM
	msdb.dbo.sysjobs AS job
	INNER JOIN
	LastSessionInformation AS lsi
	ON lsi.job_id = job.job_id
	LEFT JOIN
	msdb.dbo.sysjobhistory AS sjh
	ON sjh.job_id = job.job_id
	AND (
			(	lsi.job_history_id >= sjh.instance_id
			AND sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)
			AND sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)
			)
		OR	(	lsi.job_history_id IS NULL
			AND	sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)
			AND	sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)
			)
		)
	LEFT JOIN
	msdb.dbo.sysjobsteps AS sjs
	ON sjs.job_id = job.job_id
	AND sjs.step_id = sjh.step_id
	OUTER APPLY
	(
		SELECT
			ExecutionId = SUBSTRING(
									sjh.message,
									CHARINDEX('Execution ID: ', sjh.message) + 14,
									CHARINDEX('.', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)
									- CHARINDEX('Execution ID: ', sjh.message) - 14
								)
		WHERE
			sjs.subsystem = 'ssis'
		AND CHARINDEX('Execution ID: ', sjh.message) > 0
		AND CHARINDEX('Failed', sjh.message) = 0
		UNION ALL
		SELECT
			ExecutionId = SUBSTRING(
									sjh.message,
									CHARINDEX('Execution ID: ', sjh.message) + 14,
									CHARINDEX(',', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)
									- CHARINDEX('Execution ID: ', sjh.message) - 14
								)
		WHERE
			sjs.subsystem = 'ssis'
		AND CHARINDEX('Execution ID: ', sjh.message) > 0
		AND CHARINDEX('Failed', sjh.message) > 0
	) AS eii
	LEFT JOIN
	SsisExecution AS exe
	ON sjs.subsystem = 'SSIS'
	AND exe.execution_id = CAST(eii.ExecutionId AS INT)
	LEFT JOIN
	LatestMessage AS lem
	ON lem.operation_id = exe.operation_id
	AND lem.message_type = 120
	LEFT JOIN
	LatestMessage AS lmg
	ON lmg.operation_id = exe.operation_id
	AND lmg.message_type = 40
	LEFT JOIN
	SSISDB.catalog.event_messages AS msg
	ON msg.event_message_id = COALESCE(lem.last_message_id, lmg.last_message_id)
WHERE
	job.name LIKE 'hds%'
ORDER BY
	lsi.start_execution_date DESC, job.name, instance_id DESC

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

WITH

LastSessionId

(

SELECT

session_id = MAX(session_id), job_id

FROM

msdb.dbo.sysjobactivity

GROUP BY

job_id

LastSessionInformation

(

SELECT

sja.session_id,

sja.job_id,

sja.job_history_id,

sja.start_execution_date,

sja.last_executed_step_date,

sja.stop_execution_date,

sja.next_scheduled_run_date

FROM

msdb.dbo.sysjobactivity AS sja

INNER JOIN

LastSessionId AS lsi

ON sja.job_id = lsi.job_id

AND sja.session_id = lsi.session_id

SsisExecution

(

SELECT

exe.folder_name,

exe.project_name,

exe.package_name,

exe.process_id,

opr.status,

opr.operation_id,

exe.start_time,

exe.execution_id

FROM

SSISDB.catalog.operations AS opr

INNER JOIN

SSISDB.catalog.executions AS exe

ON opr.process_id = exe.process_id

AND opr.status IN

(2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded

WHERE

opr.object_type = 20 -- package

LatestMessage

(

SELECT

last_message_id = MAX(msg.event_message_id), msg.message_type, msg.operation_id

FROM

SSISDB.catalog.event_messages AS msg

GROUP BY

msg.operation_id, msg.message_type

)

SELECT

job.job_id,

sjh.instance_id,

job = job.name,

lsi.start_execution_date,

last_executed_date = COALESCE(lsi.stop_execution_date, lsi.last_executed_step_date),

lsi.next_scheduled_run_date,

sjh.step_id,

sjh.step_name,

sjh.run_status,

IIF(lsi.start_execution_date IS NOT NULL, COALESCE(sjh.message, '>>> RUNNING'), NULL),

package = exe.folder_name + '\' + exe.project_name + '\' + exe.package_name,

msg.message,

msg.message_source_name,

msg.execution_path,

msg.package_path,

details = 'SELECT * FROM SSISDB.catalog.event_messages WHERE operation_id = '

+ CAST(exe.operation_id AS VARCHAR(50)) + ' ORDER BY message_time DESC'

FROM

msdb.dbo.sysjobs AS job

INNER JOIN

LastSessionInformation AS lsi

ON lsi.job_id = job.job_id

LEFT JOIN

msdb.dbo.sysjobhistory AS sjh

ON sjh.job_id = job.job_id

AND (

( lsi.job_history_id >= sjh.instance_id

AND sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)

AND sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)

)

OR ( lsi.job_history_id IS NULL

AND sjh.run_date >= CAST(FORMAT(lsi.start_execution_date, 'yyyyMMdd') AS INT)

AND sjh.run_time >= CAST(FORMAT(lsi.start_execution_date, 'hhmmss') AS INT)

)

LEFT JOIN

msdb.dbo.sysjobsteps AS sjs

ON sjs.job_id = job.job_id

AND sjs.step_id = sjh.step_id

OUTER APPLY

(

SELECT

ExecutionId = SUBSTRING(

sjh.message,

CHARINDEX('Execution ID: ', sjh.message) + 14,

CHARINDEX('.', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)

- CHARINDEX('Execution ID: ', sjh.message) - 14

)

WHERE

sjs.subsystem = 'ssis'

AND CHARINDEX('Execution ID: ', sjh.message) > 0

AND CHARINDEX('Failed', sjh.message) = 0

UNION ALL

SELECT

ExecutionId = SUBSTRING(

sjh.message,

CHARINDEX('Execution ID: ', sjh.message) + 14,

CHARINDEX(',', sjh.message, CHARINDEX('Execution ID: ', sjh.message) + 14)

- CHARINDEX('Execution ID: ', sjh.message) - 14

)

WHERE

sjs.subsystem = 'ssis'

AND CHARINDEX('Execution ID: ', sjh.message) > 0

AND CHARINDEX('Failed', sjh.message) > 0

) AS eii

LEFT JOIN

SsisExecution AS exe

ON sjs.subsystem = 'SSIS'

AND exe.execution_id = CAST(eii.ExecutionId AS INT)

LEFT JOIN

LatestMessage AS lem

ON lem.operation_id = exe.operation_id

AND lem.message_type = 120

LEFT JOIN

LatestMessage AS lmg

ON lmg.operation_id = exe.operation_id

AND lmg.message_type = 40

LEFT JOIN

SSISDB.catalog.event_messages AS msg

ON msg.event_message_id = COALESCE(lem.last_message_id, lmg.last_message_id)

WHERE

job.name LIKE 'hds%'

ORDER BY

lsi.start_execution_date DESC, job.name, instance_id DESC