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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » The attempt to add a row to the Data Flow...

27 posts, Page 2 of 3

««1 2 3 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

derald-764731

Posted Thursday, October 20, 2011 11:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 5:20 PM
Points: 4, Visits: 167

In my case the problem was only on the items in the data flow task where the "[highlight=#ffff11]Check Constraints[/highlight]" option was selected for the OLE DB Destination. Once I change that, i had no issues. In my case I did not want the check, but your case might be different.

Regards,

Derald

Post #1193903

feersum_endjinn

Posted Tuesday, November 15, 2011 12:03 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, November 09, 2012 3:04 AM
Points: 32, Visits: 291

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

Post #1206235

Sander Stuurwold-385722

Posted Tuesday, February 21, 2012 9:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 11:19 PM
Points: 2, Visits: 25

isn't it the case that excel is full? (64000 rows exceeded?)

Post #1255373

Koen Verbeeck

Posted Wednesday, February 22, 2012 12:03 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 9,367, Visits: 6,465

Sander Stuurwold-385722 (2/21/2012)

isn't it the case that excel is full? (64000 rows exceeded?)

Remember that the latest post in this thread is already a few months old.

Furthermore, Excel is nowhere mentioned in this thread - the OP is talking about a flat file - so no, it probably isn't the case that the Excel is full.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1255723

feersum_endjinn

Posted Wednesday, February 22, 2012 4:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, November 09, 2012 3:04 AM
Points: 32, Visits: 291

I can't see how this can be an Excel error when Excel is not mentioned anywhere by the OP and also the OP is importing data to SQL Server.

As I said in my earlier reply what worked for me was to play around with those three settings. A few months after this post I started getting another related error for the largest of the source tables. In the end I had to change the insert from a SQL Server Destination / BULK insert to a OLEDB Destination and the source was a select statement (from the same table as before) but with a

 SET ANSI_WARNINGS OFF

at the start. For some reason although the error is in fact a warning, SSIS stops executing the task and logs it as a task error.

Post #1255835

Sander Stuurwold-385722

Posted Wednesday, February 22, 2012 5:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 11:19 PM
Points: 2, Visits: 25

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

Post #1255864

khazrouni

Posted Wednesday, February 29, 2012 2:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:22 AM
Points: 3, Visits: 23

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+

Post #1259363

Koen Verbeeck

Posted Wednesday, February 29, 2012 2:57 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 9,367, Visits: 6,465

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.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1259365

khazrouni

Posted Wednesday, February 29, 2012 3:20 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:22 AM
Points: 3, Visits: 23

Google Traduction fera l'affaire Wink

Post #1259387

feersum_endjinn

Posted Wednesday, February 29, 2012 3:22 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, November 09, 2012 3:04 AM
Points: 32, Visits: 291

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

Post #1259389

« Prev Topic | Next Topic »

27 posts, Page 2 of 3

««1 2 3 »»

Permissions