Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL-Agent Job succeeded - Despite of errors in step1 Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 9:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:51 AM
Points: 80, Visits: 41
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
Posted Friday, August 24, 2012 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 14, 2014 10:03 PM
Points: 3, Visits: 43
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
Posted Friday, August 24, 2012 9:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:51 AM
Points: 80, Visits: 41
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
Posted Friday, August 24, 2012 2:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 7,135, Visits: 12,746
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


  Post Attachments 
ExecutionOptions.jpg (101 views, 24.47 KB)
Post #1349936
Posted Monday, August 27, 2012 1:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:51 AM
Points: 80, Visits: 41
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
Posted Monday, August 27, 2012 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 7,135, Visits: 12,746
You didn't answer my question.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1350361
Posted Monday, August 27, 2012 7:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:51 AM
Points: 80, Visits: 41
sry, it looks like in your picture. I've got no Execution Option set.
Post #1350367
Posted Monday, August 27, 2012 10:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1350463
Posted Tuesday, August 28, 2012 3:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 7:51 AM
Points: 80, Visits: 41
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 »

Add to briefcase

Permissions Expand / Collapse