Search code examples
ssissyntax-erroroledboledbexception

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14

Why I can't use same connection in loop. Could you help me, please? I would explain step of all.

Step is as following:

  1. "Get By Brand" block(Execute SQL Task): I query Brand data, output is object. For example the output is "AAA", "BBB" - The loop next step will run for two time.
  2. "Foreach Loop by All Brand" block: First round of loop is passed. Second round of loop is not. Even though "Gen Header file" block(Data Flow Task) isn't change from first round - Connection and all is same from first round.

Second round of loop is error: Second round of loop is error

The detail of "Gen Header file" block(Data Flow Task): The detail of "Gen Header file" block(Data Flow Task)

The detail of "Gen Header file" block(Data Flow Task), Variable name User::SQL_P_SCRIPT is also not change from first round: The detail of "Gen Header file" block(Data Flow Task)

Error message is as following:

Information: 0x4004300A at Gen Header file, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Gen Header file, OLE DB Source revise [12072]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80040E37  Description: "ORA-00942: table or view does not exist
".
Error: 0xC004706B at Gen Header file, DTS.Pipeline: "component "OLE DB Source revise" (12072)" failed validation and returned validation status "VS_ISBROKEN".
Error: 0xC004700C at Gen Header file, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Gen Header file: There were errors during task validation.
Warning: 0x80019002 at Foreach Loop by All Brand: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at RP_PROD_SpareServer102002211: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "RPInStaySurvey.dtsx" finished: Failure.
Solution

  • Since I use same connection in loop for many times. Therefore, I set property of connection name "RetainSameConnection" to True (Default is False). The problem is solved.