Hello,
I am using the script below to find the package name which have an eventName which are equal to OnError or OnTaskFailed.
However, I am not able to link that table to the SQL Agent jobs or SQL Agent Jobs Steps History
Does someone knows how to do that?
package...
Select A.*
from
(
Select @@SERVERNAME AS [Server]
,o.Object_Name [Project Name]
,Replace(e.package_name , '.dtsx', '') [Package Name]
,o.start_time [Start Time]
,o.end_time [End Time]
,e.message_source_name [Message Source Name] --[Tool (Step)]
,e.event_name [Event Name]
,e.subcomponent_name [SubComponent Name]
,e.message_code [Message Cod]
,m.message_time [Event Time] --[Error Date]
,m.message [Error Message]
,m.message_time [Error Date]
,o.caller_name [Caller Name]
,o.Stopped_By_Name [Stoped By]
,Row_Number() Over(Partition BY m.operation_id Order By m.message_source_type Desc) [Source Type Order]
From SSISDB.internal.operations o
Join SSISDB.internal.operation_messages m
On o.operation_id = m.operation_id
Join SSISDB.internal.event_messages e
On m.operation_id = e.operation_id
And m.operation_message_id = e.event_message_id
Where (m.message_type = 120 or m.message_type = 130)
) A
Job step history
Select A.*
, (run_duration_total_seconds/60.0) as 'Step Duration (Minutes)'
, ([Run Date] + ' ' + [Run Time]) as 'Step Run Date Time'
from
(
SELECT [instance_id]
,[sJOBH].[job_id]
,[step_id]
,CONCAT(@@SERVERNAME ,'|',[sJOBH].[job_id],'|',cast(step_id as nvarchar)) as [Step Index ID]
,[step_name]
,[sql_message_id]
,[sql_severity]
,[message]
,[run_status]
,[run_date]
,[run_time]
,[run_duration]
,[operator_id_emailed]
,[operator_id_netsent]
,[operator_id_paged]
,[retries_attempted]
,[server]
,CASE
WHEN run_status = 0 THEN 'Failed'
WHEN run_status = 1 THEN 'Succeeded'
ELSE 'Cancelled'
END AS 'Run Status'
,STUFF(STUFF(CAST(run_date as varchar(8)),5,0,'-'),8,0,'-') AS 'Run Date'
,CASE
WHEN LEN(run_time)< 6 THEN STUFF(STUFF(REPLICATE('0',(6-LEN(run_time))) + CAST (run_time as varchar(6)),3,0,':'),6,0,':')
ELSE STUFF(STUFF(CAST (run_time as varchar(6)),3,0,':'),6,0,':')
END AS 'Run Time'
,run_duration/10000 as run_duration_hours_only --hours
,run_duration/100%100 as run_duration_minutes_only --minutes
,run_duration%100 as run_duration_seconds_only --seconds
,(run_duration/10000 * 60 * 60) + -- hours as seconds
(run_duration/100%100 * 60) + --minutes as seconds
(run_duration%100 ) as run_duration_total_seconds --seconds
,[sJOB].[Name] as [Job Name]
FROM [msdb].[dbo].[sysjobhistory] AS sJOBH
LEFT JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJOB].[job_id] = [sJOBH].[job_id]
where step_id<>0
) A
/* Excluding Job Name we don't want information about*/
Where [Job Name] not like '%OLA%'
AND [Job Name] not like '%SSIS%'
AND [Job Name] not like '%syspolicy%'
