Wednesday, January 26, 2011

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

I know this doesn't make a lot of sense so far, but from what I can tell, the Fast Load OLE DB Destination mode can fail if an unconnected ADO destination in the same Data Flow also fails.  

Got this error in SSIS with an OLE DB Destination:
"The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020."
This error only popped up when an error in an unrelated transformation in the same data flow had an error.  I stopped receiving this error when I changed from a "Table or View - fast load" to "Table or View".

That's the short of it.  Here's more:

This is a puzzling error apparently caused by an error with a transformation with an ADO NET Destination, that runs concurrently and in parallel - that is, unconnected - to my OLE DB Destination.

I don't really have the time to figure out all the different permutations that allow this error to keep coming up, but safe to say its not really an error... it seems to be caused by other errors, but the important thing to note is that an error in one transformation was able to allow an unrelated transformation to fail.  This can be very misleading when trying to debug a screen full of red blocks.

The error that came up was a run of the mill aggravating data type error caused by SSIS being picky -
[SLRP ADO [4902]] Error: An exception has occurred during data insertion, the message returned from the provider is: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.
Will keep an eye out for more stuff like this in SSIS, open to feedback.

4 comments:

Anonymous said...

I just got this error. In my case, it was the excel driver trying to pump too many rows into excel.

Anonymous said...

check with the derived column and data conversion transformations because some data types are didn't convert from one to another that time this errors will be occurred.

kiran said...

There will be mismatch datatype value in the source

Unknown said...

this works for me for when i changed table view fast load to table or view