SQL-Agent Job succeeded - Despite of errors in step1

Question

SQL-Agent Job succeeded - Despite of errors in step1

michael.reichart

SSC Veteran

Points: 264
More actions
August 24, 2012 at 9:07 am

#265921

I have a SQL-Agent Job which executes a SSIS-Package. There are Errors in the SSIS-Pakage, but the Job is succeeded.
Here the log for the step:
Message
Executed as user: XXXXX. ....50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 13:53:14 Error: 2012-08-24 13:53:14.97 Code: 0x800470D8 Source: ZEBRA_Stammdaten Description: The result string for expression "@[User::DestPath] + "ZEBRA_Stamm_" + REPLICATE("0",2 - LEN((DT_STR,1,1252)MONTH(GETDATE())))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR. End Error The package executed successf... The step succeeded.
So the problem with the SSIS-Package is fixed, but i wonder why the Job is successful despite of this error. Is there a way to avoid this behavior, so that the SQL-Agent Job will fail?

Viewing 9 posts - 1 through 8 (of 8 total)

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

epescett Old Hand Points: 379 More actions · Answer 1

To make the job fail I think you would have to make that a fatal error in the SSIS package. It looks like the truncation just gave you a warning? Have you tried setting that field to fail on truncation?

michael.reichart SSC Veteran Points: 264 More actions · Answer 2

When I start the SSIS Package in BIDS I will get an Package Validation Error, and it will not run at all. So I think there will be no difference. My Problem is that this error is not shown in the Job Activity Monitor.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

may be truncated if it exceeds the maximum length of 4000 characters

This is a validation message based on SSIS not knowing if the result of an Expression in your Package will be exceed 4000 characters in length and be truncated at runtime. This is because the result of an Expression depends on runtime value of the involved Variables, i.e. the data, which SSIS cannot predict. What do you have for Execution Options in your job?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

michael.reichart SSC Veteran Points: 264 More actions · Answer 4

Thank you for your reply. I know this option, but unfortunately we have some other Validation warnings in the package, which are ok and will not force the package to fail. So I wounder why the SQL-Agent Job makes no difference between Validation Warnings and Validation Errors.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

You didn't answer my question.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

michael.reichart SSC Veteran Points: 264 More actions · Answer 6

sry, it looks like in your picture. I've got no Execution Option set.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

Thanks for posting back. As far as I know the validation errors you are seeing are considered non-fatal so SQL Agent carries on as usual. In your package you can set DelayValidation to True at the package level, or at the Container level as close to the Task where your Expression resides, to avoid any failures in BIDS.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

michael.reichart SSC Veteran Points: 264 More actions · Answer 8

Thank u, that was the Answer I want to hear 🙂 . Your hint with the delayValidation Property works fine. But your 2nd sentence was the answer of my question

As far as I know the validation errors you are seeing are considered non-fatal so SQL Agent carries on as usual.

This is right, the problem why I didn't find the expected results was, that someone changed the Package Configuration to another path. So the Package works - despite of the error.

Thank you