SQL Server Agent runs job successfully but reports failure

Question

SQL Server Agent runs job successfully but reports failure

paul.farnell

SSC Eights!

Points: 806
More actions
April 9, 2015 at 6:41 am

#302841

I have an SQL server agent job running a number of SSIS jobs. All steps run successfully but one of the reports a failure. The strange thing is that the job which truncates a table and re-populates from a text file actually completes successfully. The error message created is
The return value was unknown. The process exit code was 2. The step failed.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11642 More actions · Answer 1

I've had similar issues in the past. The problem is often the errors are very vague. Try editing the advanced option on your job step to 'Include step output in history' and also 'log to table'.

Then run the job again then you can do:

EXEC dbo.sp_help_jobsteplog @job_name = N'myJob';

This might give you a more descriptive problem as to why the step is failing.

SELECT quote FROM brain WHERE original = 1
0 rows returned

paul.farnell SSC Eights! Points: 806 More actions · Answer 2

Thanks for this, unfortunately it tells me it has completed (which it has) . . . ?

Microsoft (R) SQL Server Execute Package Utility

Version 11.0.2100.60 for 64-bit

Started: 4:53:12 PM

DTExec: The package execution returned DTSER_COMPLETION (2).

Started: 4:53:12 PM

Finished: 4:53:21 PM

Elapsed: 9.531 seconds

Y.B. SSChampion Points: 11642 More actions · Answer 3

How many steps do you have in your job and which one is reporting an error? The logging is applied to each step individually, so you'll need to make sure you at least get the output of the step that is returning with an error.

SELECT quote FROM brain WHERE original = 1
0 rows returned

paul.farnell SSC Eights! Points: 806 More actions · Answer 4

I have moved the step to be the last one and even set it going from the last step with the same outcome.

Y.B. SSChampion Points: 11642 More actions · Answer 5

Shouldn't have any reason to move the step. Just apply the logging to the one in question.

Maybe try restarting the Agent and running again. I've seen my share of oddities that was simply fixed this way.

SELECT quote FROM brain WHERE original = 1
0 rows returned

paul.farnell SSC Eights! Points: 806 More actions · Answer 6

Managed to get to the bottom of this. So the SQL Server Agent job is failing with a DTS_Exec_Result of ‘2’ for ‘completion’ but appears to be completing the steps OK. The SQL Server Agent expects a DTS_Exec_Result of ‘0’ for ‘success’ or ‘1’ for ‘failure’, see following link:

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsexecresult.aspx

The DTS_Exec_Result can be forced in the SSIS package while in Visual Studio

In this case, the ‘ForceExecutionResult’ was set to ‘completion’ which was causing the SQL Agent Job to fail. Assuming the package normally runs fine this should be set to ‘none’ as the ‘DTS_Exec_Result’ is determined by the output of the package.