Query to check Integration failed jobs error message.
Recently i have used SSIS package and deployed this package to one of SSIS server. now i have created a scheduled job to execute this package for time intervals. After some execution job failed. As per our regular terms i have checked job’s history to get error message, but some how its not giving me a correct message.
Error Message from Job History.
Executed as user: DomainName\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:08:00 AM Package execution on IS Server failed. Execution ID: 3403914, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 2:08:00 AM Finished: 2:09:05 AM Elapsed: 64.99 seconds. The package execution failed. The step failed.
Before this deployed version if SSIS, i am using filesystem package method and if job fails in this filesystem deployment task it shows me the error correctly. Now how can i get the error for deployed package jobs.
Solution : i have created a query to get error message for last failed schedule for particular job.
DECLARE @Full_ssis_command VARCHAR(4000),
@Job_step_id INT,
@Package_name VARCHAR(4000),
@Tablehtml NVARCHAR(MAX),
@Mailsubject VARCHAR(200),
@Job_name VARCHAR(100),
@Job_id UNIQUEIDENTIFIER
SELECT @Job_id = Job_id
FROM MSDB.DBO.Sysjobs
WHERE Name = 'JobName'
SELECT TOP 1 @Job_step_id = Step_id
FROM MSDB.DBO.Sysjobhistory (NOLOCK)
WHERE Run_status <> 1
AND Step_id > 0
AND Job_id = @Job_id
ORDER BY Instance_id DESC
SELECT @Full_ssis_command = Command
FROM MSDB.DBO.Sysjobsteps (NOLOCK)
WHERE Job_id = @Job_id
AND Step_id = @Job_step_id
IF @Full_ssis_command LIKE '%.dtsx%'
BEGIN
SELECT @Package_name = RIGHT(LEFT(@Full_ssis_command, Charindex('.dtsx', @Full_ssis_command)-), Charindex('\', Reverse(LEFT(@Full_ssis_command, Charindex('.dtsx', @Full_ssis_command)-1)))-1) + '.dtsx''
END
SELECT [Message_time],
[Extended_info_id],
[Package_name],
[Message_source_name],
[Subcomponent_name],
[Package_path],
[Execution_path],
LEFT([Message], 400)
FROM SSISDB.[catalog].[Event_messages] (NOLOCK)
WHERE [Package_name] = @Package_name
AND Event_name = 'OnError'
AND Message_time >= DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
AND Operation_id IN (SELECT Max(Operation_id)
FROM SSISDB.[catalog].[Event_messages](NOLOCK)
WHERE [Package_name] = @Package_name)
ORDER BY Message_time ASC
Thanks & Regards,
Nirav Gajjar
