September 30, 2010 at 11:04 am
I have a SSIS package where I am importing data from a remote server. I have the Precedent constraint on a sequence container set as "OnCompletion" so that if it fails it will continue to the next. I'm getting the following error. When it errors the job which the package is called fails. Any ideas?
Message
Executed as user: MED-EDGE.COM\_SVC_SQL_Acct. ...2.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:36:42 AM Error: 2010-09-30 10:33:31.63 Code: 0xC0202009 Source: Data Flow Task HPSITE_DX [121]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream". An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream". An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream". An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available.
Source: "Microsoft SQL Native Client"... The package execution fa... The step failed.
September 30, 2010 at 11:11 am
I'm not 100% sure I understand what you're saying the problem is. If you complaining that the execution stops at the errror and does not execute the next task then I think you might need to change the value for the MaximumErrorCount for the package.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 30, 2010 at 11:19 am
I have the MaximumErrorCount = 10.
September 30, 2010 at 11:29 am
Are FailPackageOnFailure and FailParentOnFailure set to FALSE for the failing item?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 30, 2010 at 11:56 am
Is it possible that the "ErrorCount" value being checked is the error count for the entire package and not just for that task? I had a SendEmail task with its MaxErrorCount = 1 thinking that it would only fail if the number of errors for that task is 1. Is it seeing the total errors for the package as 1 and sending the email the causing the package to fail?
September 30, 2010 at 12:07 pm
dndaughtery (9/30/2010)
Is it possible that the "ErrorCount" value being checked is the error count for the entire package and not just for that task? I had a SendEmail task with its MaxErrorCount = 1 thinking that it would only fail if the number of errors for that task is 1. Is it seeing the total errors for the package as 1 and sending the email the causing the package to fail?
Each item has it's own error count. The total number of errors that occurred in the package should not affect the individual tasks. They should only be affected by theire own failures, with the exception of containers who are affected by failures of the tasks they contain. Your original assumpion for the SendMail task seems correct. It should only be affected by it's own failures.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 30, 2010 at 12:11 pm
Is there a possibility that the SendEmailTask sent the email then failed afterwards? I recieved an email stating that one of the sequence containers failed (The Email task is connected by an onfailure precedence Constraint from the sequence Container) then my Job failed.
October 1, 2010 at 7:18 am
I have a different error now: "VS_NEEDSNEWMETADATA" which is resulting from the datacolumn being changed on the database I'm pulling from but with the following properties set it causes the Job to fail when I want it to send an email notifying me that part failed and move on to the next Sequence container. As I stated before the Sequence container has an OnFailure path which goes to the SendEmailTask and an OnCompletion path pointing to the next container. This time it didn't send the email, it just crashed. Below is the error as well.
Sequence Container Properties
Delay Validation = False
Disable = false
DisableEventHandlers = False
FailPackageOnFailure = False
FailParentOnFailure = False
ForcedExecutionValue = 0
ForcedExecutionValueType = Int32
ForcedExecutionResult = None
ForcedExecutionValue = False
IsolationLevel = Serializable
LocaleID = English
Logging Mode = UseParentSetting
MaximumErrorCount = 1000
TransactionOption = Supported
The Dataflow with the error properties
DefaultBufferMaxRows = 10000
DefaultBufferSize = 10485760
Delay Validation = False
Disable = false
DisableEventHandlers = False
EngineThreads = 5
ExecuteValueVariable = <none>
FailPackageOnFailure = False
FailParentOnFailure = False
ForcedExecutionValue = 0
ForcedExecutionValueType = Int32
ForcedExecutionResult = None
ForcedExecutionValue = False
IsolationLevel = Serializable
LocaleID = English
Logging Mode = UseParentSetting
MaximumErrorCount = 1000
RunInOptimizedMode = True
TransactionOption = Supported
The error:
Date9/30/2010 8:28:08 PM
LogJob History (Transfer MedA4 Server Data to MEDBIDATA)
Step ID6
ServerMEDBIDATA
Job NameTransfer MedA4 Server Data to MEDBIDATA
Step NameRun Package 6
Duration00:00:08
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: MED-EDGE.COM\_SVC_SQL_Acct. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:28:08 PM Error: 2010-09-30 20:28:16.12 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "HPSITE_DEMOGRAPHICS" (106)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". End Error Error: 2010-09-30 20:28:16.12 Code: 0xC004700C Source: Data Flow Task DTS.Pipeline Description: One or more component failed validation. End Error Error: 2010-09-30 20:28:16.12 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:28:08 PM Finished: 8:28:16 PM Elapsed: 8.265 seconds. The package execution failed. The step failed.
October 1, 2010 at 7:54 am
Try setting the DelayValidation property to FALSE for the problem causing item.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 1, 2010 at 8:10 am
I have Sequence container which holds a DataFlow which holds a number of sources which I want to turn off Validation. If I set the DelayValidation Property of the Sequence Container to true will that stop the validation for all the sources within the dataflow which is a child of the sequence container?
October 1, 2010 at 8:36 am
dndaughtery (10/1/2010)
I have Sequence container which holds a DataFlow which holds a number of sources which I want to turn off Validation. If I set the DelayValidation Property of the Sequence Container to true will that stop the validation for all the sources within the dataflow which is a child of the sequence container?
I would have guessed otherwise, but I found a reference in an article that states that setting the Delay validation for the container will affect the task inside it.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 1, 2010 at 11:05 am
SUCCESS!!!
Turns out you can change the DelayValidation for the package and it is scoped down to everything in the package.
Also needed to make sure every task, container, or "anything" else with a MaximumErrorCount property (that means eery single one regardless if its a child or not) was set to a number that would never be met. I had set it to 1000.
Thanks Alvin for all of your help!
October 1, 2010 at 11:16 am
oops, didn't see the posts on the second page and had double posted the preceding post.
October 1, 2010 at 11:52 am
Great
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply