Error : The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020

Question

 Hi All,

I have a package that transform data from OLEDB Source TableA to OLEDBDestination TableB.
The count of records in TableA is around 7.500.000 records.
When I run that package, the package run well, but after around 200.000 records have been inserted to TableB, the package got error, and TableA become Red. This is the summary of the error messages :

Unspecified error

SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All 11" (16989) failed with error code 0xC0047020 while processing input "Union All Input 2" (17111). 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.

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020

I've tried to search that problem, and I read in forum that someone suggest to change the Data Access Mode for OLEDB Destination From "Table/View Fast Load" to "Table/View". I've tried that, but the performance become very slow, and I still got the same error after around 300.000 records have been inserted to TableB.

Does anybody know what reason that might cause that error?

Thank you

Answer 1

where did you got this error? on source? destination ? or transformation?

you should configure error output on any component which caused error, and redirect error output to another flat file destination, then you can run the package, and take a look at bad data rows in flat file to troubleshoot problem

---

http://www.rad.pasfu.com

Answer 2

You need to post all of the error messages you receive in the Output window - not just one of them.  The error you've posted relates to a Union All component, which I'm quite certain isn't related to your actual problem.

---

Talk to me now on

Answer 3

Hi All,

I got this error on the source and destination, not because of the transformation (Union All Component).

Sorry, I wanted to summarize the error messages because the log event displayed the same error message for several times, but I missed something, this is the error messages :

The ProcessInput method on component "Union All 12" (8507) failed with error code 0xC0047020 while processing input "Union All Input 1" (8508). 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.

 INSERT TABLEA TO TABLEB {60378B3F-72AA-4B1B-BFB4-6ED915AB7B54} {A2115E0C-B659-4865-8D5E-1192ACB1319F} SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "TABLEA" (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.

 INSERT TABLEA TO TABLEB {60378B3F-72AA-4B1B-BFB4-6ED915AB7B54} {A2115E0C-B659-4865-8D5E-1192ACB1319F} The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

 

But now that errors can be solved by using SQL Server Destination instead of using OLEDB Destination, and set the TIME OUT to higher value (for ex : 500)

But I'm still wondering why I need to do this. It seems that the connection to the destination database timed out. Is it possible because I have quite complicated transformation and it takes time to pass the records from Source to Destination?

And We can't use SQL Server Destination if the Destination database is not in the same server with our package, right? Can we set TIME OUT in OLEDB Destination?, I have tried to set properties in OLEDB Destination ROW_PER_BATCH = 100 and MAXIMUM_INSERT_COMMIT=100, but I still got that error messages :(

Thank you for your help.

Regards,

Ratna

 

 

 

 

 

 

Answer 4

There shouldn't be an issue with timeouts and the OLE DB Destination.

The error message you've posted indicates there are other error messages from earlier in the data flow - can you please post those?

What options do you have set on your OLE DB Destination?

---

Talk to me now on

Answer 5

I used OLEDB Destination with Data Access Mode "Table or View - Fast Load".

The error message before that error messages only this:

08-Jul-11 1:17:29 PM 08-Jul-11 1:17:29 PM
OnError Package11 {02EFADA7-B576-4D27-A297-7AECB1F8A8B5} {A2115E0C-B659-4865-8D5E-1192ACB1319F} Unspecified error
 08-Jul-11 1:17:30 PM 08-Jul-11 1:17:30 PM
OnError  INSERT TABLEA TO TABLEB {60378B3F-72AA-4B1B-BFB4-6ED915AB7B54} {A2115E0C-B659-4865-8D5E-1192ACB1319F} SSIS Error Code

I guess the problem is in Time Out, because after I changed OLEDB Destination to SQL Server Destination, I still got that error, but after I changed Time Out to higher value, the package can be executed successfully and all the records are inserted to destination table.

Thank You.

 

Regards,

Ratna

 

Answer 6

And with OLEDB Destination, I didn't get that error message in the beginnning of package execution. Sometimes after 200.000 records have been inserted to the Destination table, I got that error messages, and sometimes after 300.000 records have been inserted to Destination table.

So I thought that's not because of data or transformation problem, but depends on the response time from the destination database connection.

Thank You.

 

Regards,

Ratna