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

Question

Post reply

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

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

blackadder SSC Veteran Points: 204 More actions · Answer 1

I AM VERY SORRY. I GOT THIS ERROR WITH EXCEL. PLEASE IGNORE MY ENTRY

khazrouni SSC Enthusiast Points: 199 More actions · Answer 2

J'ai eu le même message d'erreur.

Tu peux consulter ce lien pour voir la solution.

http://www.atawasol.com/ssis-f14/topic187.html

A+

Koen Verbeeck SSC Guru Points: 259095 More actions · Answer 3

khazrouni (2/29/2012)
J'ai eu le même message d'erreur.
Tu peux consulter ce lien pour voir la solution.
http://www.atawasol.com/ssis-f14/topic187.html
A+

Je ne pense pas que beaucoup de gens va te comprendre ici, surtout quand la plupart parle seulement Anglais.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

khazrouni SSC Enthusiast Points: 199 More actions · Answer 4

khazrouni

SSC Enthusiast

Points: 199

February 29, 2012 at 3:20 am

#1453020

Google Traduction fera l'affaire 😉

feersum_endjinn SSCommitted Points: 1966 More actions · Answer 5

Then why not use Google Translate yourself and post your reply in English?

Koen Verbeeck SSC Guru Points: 259095 More actions · Answer 6

khazrouni (2/29/2012)
Google Traduction fera l'affaire 😉

Pour ceux qui savont que c'est Français oui 🙂

(moi, je parle Français, je n'ai pas besoin de ce site)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

khazrouni SSC Enthusiast Points: 199 More actions · Answer 7

Je savais pas que cela posera un tel problème. La prochaine fois je posterai en Anglais (et même en chinois si vous voulez :-D)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

feersum_endjinn (11/15/2011)
In my case this was caused due to the following properties
[Data Flow Task]
DefaultBufferMaxRows
DefaultBufferSize
[SQLServerDestination]
MaxInsertCommitSize
You need to play around with those three parameters and get the correct values for your hardware/software/database configuration. First try the MaxInsertCommitSize parameter - this is set to 0 by default which means during a bulk insert that it performs one transaction (COMMIT) for the bulk insert. This might cause buffer issues if you don't have enough memory for a large dataset. In my case it was a transfer of 11 million rows that failed and I have now set the parameter to 250,000. MaxRows is set to 10,000 and BufferSize is set to 104857600 (100MB). As the articles below advise, try to avoid swapping to disc at all costs.
Read these articles for some very useful tips:
Top 10 SQL Server Integration Services Best Practices
Improving the Performance of the Data Flow

I'm having the same problem with several tables out of 69.

I get the following error:

[CTL_SUMMARY_MASTER [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

There are 44,545 records in the source table. I have 24 GB of Memory on the machine.

DefaultBufferMaxRows: 10000

DefaultBufferSize: 10485760

Rows per Batch: 5000

MaximunInsertCommitSize: 50000

I tried changing things but no luck.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

I switched from Fast Load to Table or View and executed the task and it succeeded but when I ran the package it failed..

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

rasika.segan SSC Enthusiast Points: 167 More actions · Answer 10

There are two things you need to follow:

a. If you using a SQL server destination, switch it to OLEDB destination.

b. Use the Table/View load instead of Fast load.

This would help solve the issue 🙂

Eric Mamet SSChampion Points: 11728 More actions · Answer 11

Is there not a big difference in speed between FastLoad and "Table or View"?

I am experiencing a problem with the same error message but I am loading ~100 million (fat) rows and it took about 50mns before it decided not to work anymore.

I tried running it in BIDS on my workstation and it worked (although took 2 hours)... but failed again when fired through sql Server Agent (hence on the server)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

rasika.segan (7/10/2012)
There are two things you need to follow:
a. If you using a SQL server destination, switch it to OLEDB destination.
b. Use the Table/View load instead of Fast load.
This would help solve the issue 🙂

I also use OLEDB as the destination.

Yes, when I encounter this error I have switched from Fast Load but in most cases I still get the error.

There are non-printable characters in the data source and this is the source of the problem.

This typically happens in Comment related tables.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jessica_ST SSC Eights! Points: 912 More actions · Answer 13

For my part, this was caused by a conditional split failing, my condition was evaluation to NULL when comparing date of payment to the SCD valid from date on SCDs to pull out late arriving facts. I had [foolishly] assumed that batch lines would not have a null payment date. :ermm: Anyhow, upshot is the data flow was failing at the split and this appears to have stopped the buffer from clearing, the datasource then failed when it could not add new rows into the buffer. (Note: the preceding is all assumption, I've not taken the time to analyse the internals of what actually happened).

Hope this helps.

Assumption is the mother of all F***ups

Jessica_ST SSC Eights! Points: 912 More actions · Answer 14

and before anyone else says it, Yes, I should have been following my own motto! 😉

Assumption is the mother of all F***ups

o103452@rtrtr.com Ten Centuries Points: 1025 More actions · Answer 15

I had the same problem and tried all mentioned methods but all failed. At the end, I realised that the source column size was exceeding the size of the item in conversion component. After setting them equal, the problem had beeen solved.