March 4, 2014 at 9:37 am
I have a SSIS package executing (32-bit) within a SQL Server Agent job. The 'Retry Attempts' is sent to 4 times every hour (if the Database I'm extracting from is down for maintenance I want to try for up to 4 hours) on the Step in the SQL Server Agent job.
Sometimes I get the following warning/error:
Executed as user: sqladmin. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:15:00 AM Error: 2014-03-01 04:19:15.52 Code: 0xC0202009 Source: Insert Result Records message_details [160] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". End Error Error: 2014-03-01 04:19:15.52 Code: 0xC020901C Source: Insert Result Records message_details [160] Description: There was an error with input column "MYCol" (232) on input "OLE DB Destination Input" (173). The column status returned was: "The value could not be converted because of a potential loss of data.". End Error Error: 2014-03-01 04:19:15.52 Code: 0xC0209029 Source: Insert Result Records message_details [160] Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (173)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (173)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. End Error Error: 2014-03-01 04:19:15.52 Code: 0xC0047022 Source: Insert Result Records SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "message_details" (160) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (173). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. End Error Error: 2014-03-01 04:19:15.52 Code: 0xC0209017 Source: Insert Result Records tmp_message_details [1] Description: Setting the end of rowset for the buffer failed with error code 0xC0047020. End Error Error: 2014-03-01 04:19:15.52 Code: 0xC0047038 Source: Insert Result Records SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "tmp_message_details" (1) returned error code 0xC0209017. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:15:00 AM Finished: 4:19:15 AM Elapsed: 254.797 seconds. The package execution failed.
Now I know what the Error is and I'm expecting the Step to go to the 'On Failure Action' Step I have specified within my Agent job. But that's not what's happening. It appears the Flow Task the error occurs in is stopping at that point and then moves on to the next Flow Task in the Control Flow, thus completing the Package. When the Package completes the Agent job thinks it needs to Re-try, so an hour later it does.
I want this Package that when this error occurs it 'abends' Stops executing the package at that point. Is there a setting within the Package or the Flow Task I need to set to accomplish this?
Thanks,
Sqlraider
March 4, 2014 at 11:39 am
I think you need to look at FailPackageOnFailure and/or FailParentOnFailure properties at the package level.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2014 at 1:18 pm
Jack Corbett (3/4/2014)
I think you need to look at FailPackageOnFailure and/or FailParentOnFailure properties at the package level.
This gave me a place to start.
For my Package/Process I think I want the FailPackageOnFailure set to True on the Data Flow level and FailParentOnFailure set to True on the Package Level.
Thanks,
Sqlraider
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply