Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. Expand / Collapse
Author
Message
Posted Thursday, October 20, 2011 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:47 PM
Points: 4, Visits: 181
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
Posted Tuesday, November 15, 2011 12:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 4:30 AM
Points: 32, Visits: 292
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
Posted Tuesday, February 21, 2012 9:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:53 AM
Points: 2, Visits: 45
isn't it the case that excel is full? (64000 rows exceeded?)

Post #1255373
Posted Wednesday, February 22, 2012 12:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 13,275, Visits: 11,061
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
Posted Wednesday, February 22, 2012 4:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 4:30 AM
Points: 32, Visits: 292
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
Posted Wednesday, February 22, 2012 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:53 AM
Points: 2, Visits: 45
I AM VERY SORRY. I GOT THIS ERROR WITH EXCEL. PLEASE IGNORE MY ENTRY
Post #1255864
Posted Wednesday, February 29, 2012 2:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 4:32 AM
Points: 3, Visits: 25
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
Posted Wednesday, February 29, 2012 2:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 13,275, Visits: 11,061
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
Posted Wednesday, February 29, 2012 3:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 4:32 AM
Points: 3, Visits: 25
Google Traduction fera l'affaire
Post #1259387
Posted Wednesday, February 29, 2012 3:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 4:30 AM
Points: 32, Visits: 292
Then why not use Google Translate yourself and post your reply in English?
Post #1259389
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse