SQL-Agent Job succeeded - Despite of errors in step1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » SQL-Agent Job succeeded - Despite of errors...

SQL-Agent Job succeeded - Despite of errors in step1

Rate Topic

Display Mode

Topic Options

Author

Message

michael.reichart

Posted Friday, August 24, 2012 9:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 3:07 AM
Points: 5, Visits: 20

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?

Post #1349732

epescett

Posted Friday, August 24, 2012 9:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:03 PM
Points: 3, Visits: 41

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?

Post #1349746

michael.reichart

Posted Friday, August 24, 2012 9:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 3:07 AM
Points: 5, Visits: 20

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.

Post #1349763

opc.three

Posted Friday, August 24, 2012 2:45 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 6,722, Visits: 11,762

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post Attachments

ExecutionOptions.jpg (67 views, 24.47 KB)

Post #1349936

michael.reichart

Posted Monday, August 27, 2012 1:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 3:07 AM
Points: 5, Visits: 20

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.

Post #1350236

opc.three

Posted Monday, August 27, 2012 6:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 6,722, Visits: 11,762

You didn't answer my question.

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1350361

michael.reichart

Posted Monday, August 27, 2012 7:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 3:07 AM
Points: 5, Visits: 20

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

Post #1350367

opc.three

Posted Monday, August 27, 2012 10:02 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 6,722, Visits: 11,762

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1350463

michael.reichart

Posted Tuesday, August 28, 2012 3:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 3:07 AM
Points: 5, Visits: 20

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

Post #1350755

« Prev Topic | Next Topic »

Permissions