Home Dashboard Directory Help
Search

SSIS Package failes with OLE DB error when using transaction support on sequence container. by luisefigueroa


Status: 

Active


5
1
Sign in
to vote
Type: Bug
ID: 573638
Opened: 7/7/2010 12:15:47 PM
Access Restriction: Public
1
Workaround(s)
view
3
User(s) can reproduce this bug

Description

I am using sql server 2008 R2 x64 CU2. I have a data flow in a very simple SSIS Package. within it, I have one OLE DB data source that points to a remote server (Server A), a multicast component and two OLE DB data destination components. the destinations are two tables in the same database in a remote server (Server B). If i make transaction support required for the dataflow i get the following errors:

Error: 0xC0202009 at Data Flow Task, OLE DB Destination 1 1 1 [250]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Warning: Fatal error 3624 occurred at Jul 7 2010 2:39PM. Note the error and time, and contact your system administrator.".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Location:     pageref.cpp:922
Expression:     IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()
SPID:         60
Process ID:     7644".

Error: 0xC0209029 at Data Flow Task, OLE DB Destination 1 1 1 [250]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (263)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (263)" 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.

Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination 1 1 1" (250) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (263). 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.

Error: 0xC0202009 at Data Flow Task, OLE DB Destination 2 1 [326]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Warning: Fatal error 3624 occurred at Jul 7 2010 2:39PM. Note the error and time, and contact your system administrator.".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Location:     pageref.cpp:922
Expression:     IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()
SPID:         62
Process ID:     7644".

Error: 0xC0209029 at Data Flow Task, OLE DB Destination 2 1 [326]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (339)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (339)" 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.

Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination 2 1" (326) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (339). 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.

Error: 0xC02020C4 at Data Flow Task, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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.

Details
Sign in to post a comment.
Posted by Microsoft on 7/8/2011 at 2:29 PM
Hello!

It appears this issue was addressed in a Cumulative Update. Please see KB 2297709 for more information.

http://support.microsoft.com/kb/2297709

Thank you,

Matt Masson
SSIS Team
Posted by Todd McDermid on 7/6/2011 at 10:01 PM
I've just received this error as well - when I first got it, I thought it was the same error in pageref.cpp I'd received before in SQL 2008 SP1 when inserting into an empty table. However, the error code and line number were slightly different... but perhaps it is similar.
In my case, I can confirm that a sequence container with transactions required that contains only a single (simple) Data Flow Task can cause this issue when inserting into an empty table. If the transaction property on the sequence container is set to Supported, inserts into an empty table work. If the transaction property is Required, inserts into a populated table work. One other very odd detail is that I CAN repro this IF and ONLY IF I "reset" the intended destination table with a TRUNCATE TABLE rather than a DELETE FROM.
The Data Flow Task reads data from a Raw file, adds one column via a Derived Column, Multicasts the flow into an unaltered OLE DB Destination (all default settings). Another arm of the multicast goes into an Aggregate, then into a Derived Column, then an OLE DB Command to update an audit table. A total of 7 stock components.
The error confirms I am using SQLNCLI10 (as referenced in your original comment, MSFT).
Relevant error message from sysssislog:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Warning: Fatal error 3624 occurred at Jul 6 2011 9:23PM. Note the error and time, and contact your system administrator.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Location: pageref.cpp:922 Expression: IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage () SPID: 141 Process ID: 1592".

If you're interested in observing and poking at the repro, please contact me for a LiveMeeting session. (The longer you wait, the less likely I'll remember the execution conditions :).)
Posted by Brett Gerhardi on 6/6/2011 at 1:28 PM
I've had this also. Writing from 2 Raw files through a Union all to an OLE DB Destination where the Data Flow is joined to a distributed transaction. Transcation included a Kimball SCD custom component and a Batch Merge custom component

As per Simon Givoni's post all I did was to turn off Table Lock and the package completed without the error.

Note that I am also running READ COMMITTED SNAPSHOT (not allow snapshot isolation) on the DB, perhaps this is important in the repro that MS couldn't do?
Posted by Dave_W_OH on 3/31/2011 at 3:00 PM
Recently had same experience. I wanted to create a transaction surrounding everything in the sequence container. Set TRANSACTION OPTION to Required; got same message. Look into MSDTC and the Firewall. I'm developing on a Windows 7 PC (SQL/VSS 2008), connecting to a Windows Server 2008 R2. Once MSDTC was started on both platforms and the Firewall opened, I was able to complete the 'transaction-based' query I needed to complete my SSIS package. Good luck!
Posted by Simon Givoni _ on 2/17/2011 at 2:50 AM
I've had this exact same problem on SQL Server 2008 R2 (10.50.1600).

It happens in "Fast Load" on OLE DB Destination. "Table Lock" has to be set (is per default).

This only happens when the table is empty.

But the same scenario applies:
- When running under Distributed Transactions (TransactionOption = Required, IsolationLevel = ReadCommitted)
Doesnt have to be within a sequence container - if the package itself has TransactionOption = Required I also failes.


Error message:

Error: 0xC0202009 at dft Insert EDW_MasterRef_Customer, dst EDW_MasterRef_Customer [110]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. ".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Location:     pageref.cpp:922
Expression:     IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()
SPID:         66
Process ID:     968".
Error: 0xC0209029 at dft Insert EDW_MasterRef_Customer, dst EDW_MasterRef_Customer [110]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (123)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (123)" 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.
Error: 0xC0047022 at dft Insert EDW_MasterRef_Customer, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "dst EDW_MasterRef_Customer" (110) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (123). 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.

Best regards
Simon
Posted by Microsoft on 8/13/2010 at 10:36 AM
Hello,

It looks like the failure is occurring in SQL Server itself – could you look at the server’s error log to see if there is further details on what could have caused the error?

I also noticed that you are using the “Microsoft OLE DB Provider for SQL Server” (MSOLEDB) instead of “SQL Server Native Client 10.0” (SQLNCLI10). MSOLEDB is an older provider - SQLNCLI10 typically performs better, and sometimes output more detailed error messages when failures like this occur. You might want to try switching to SQL Native Client provider for this scenario.

Thank you,
SSIS Team
Sign in to post a workaround.
Posted by EdgarsF on 2/22/2012 at 7:20 AM
Set Recovery model to Full. This will resolve problem. Details here - http://support.microsoft.com/kb/2297709