试试这个:
SELECT distinct
cast([sJOB].[job_id] as varchar(max)) AS execution_id
, [sJSTP].[step_name] AS executable_name
, [sJOB].[name] AS package_name
, CASE [sJSTP].[run_date]
WHEN 0 THEN NULL
ELSE
CAST(
CAST([sJSTP].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJSTP].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS start_time,
dateadd(ss, run_duration, CASE [sJSTP].[run_date]
WHEN 0 THEN NULL
ELSE
CAST(
CAST([sJSTP].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJSTP].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END) end_time
, CASE [sJSTP].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 5 THEN 'Unknown'
END AS execution_result_description
FROM
[msdb].[dbo].[sysjobhistory] AS [sJSTP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
ON [sJSTP].[job_id] = [sJOB].[job_id]
inner join [msdb].[dbo].[sysjobsteps] steps
ON [sJSTP].[job_id] = [steps].[job_id]
where [sJSTP].[run_date] <> 0
and CASE [sJSTP].[run_date]
WHEN 0 THEN NULL
ELSE
CAST(
CAST([sJSTP].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJSTP].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END between dateadd(hh, -20, getdate()) and getdate()
and [sJSTP].[step_name] not in ('(Job outcome)')
order by start_time desc
此外,我使用此查询来查看正在运行的SSIS作业的步骤结果。但是,这只显示已完成的步骤,而不是正在运行的步骤。我仍然需要找到一种SQL来查看当前正在运行的步骤并将其与此合并。
select distinct
cast(e.execution_id as varchar(max)),
e.executable_name,
e.package_name,
CONVERT(datetime, es.start_time) AS start_time
, CONVERT(datetime, es.end_time) AS end_time
, datediff(mi, es.start_time, es.end_time) [running time]
, case es.execution_result
when 0 then 'Success'
when 1 then 'Failed'
when 2 then 'Completion'
when 3 then 'Cancelled'
else cast(es.execution_result as varchar(max)) end as execution_result_description
from ssisdb.catalog.executables e
left join ssisdb.catalog.executable_statistics es
on e.executable_id = es.executable_id
and e.execution_id = es.execution_id
order by 6 desc