Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020

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

  • Wednesday, January 09, 2013 12:02 AM
     
     
    Sign In to Vote
    0

    In my SSIS package I have a OLEDB Source in one of the Data Flow Task and "Data Access Mode" is "Sql Command" with the Select statement. When I ran the process after some records were processed I'm getting the below error messages which are not pin pointing to specific errors to fix the issue. I'm having the hard time to figure out the issue. Please help me.

    [SSIS.Pipeline] Error: Unspecified error

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


    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component
     "OLE DB Source 1" (224) 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.

     

All Replies

  • Wednesday, January 09, 2013 9:45 AM
     
     
    Sign In to Vote
    0

    Hi,

    what do you have set in Data Flow properties? Especially proterties defaultBufferMaxRows and DefaultBufferSize ?

    Thanks

    Zdenek

    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu

     
  • Wednesday, January 09, 2013 3:20 PM
     
     
    Sign In to Vote
    0

    DefaultBufferMaxRows is set to : 11000

    DefaultBufferSize is set to : 11485760

    Should I change these values?

     
  • Wednesday, January 09, 2013 10:04 PM
     
     
    Sign In to Vote
    1

    Hi,

    It'shoot in the dark, but try to set ten times lower DefaultBufferMaxRows  and higher DefaultBufferSize.

    Zdenek

    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu

     
  • Wednesday, January 09, 2013 10:14 PM
     
     
    Sign In to Vote
    0

    Check the ole db source first. By this I mean does your ole db source turn green all the way that means it has pulled all the required records from the source. If it turns red, paste the query in ssms and see what is the error ?

    Thanks,

    hsbal

     
  • Thursday, January 10, 2013 3:03 AM
     
     
    Sign In to Vote
    0

    I'll fire again.

    If your rows are a page each (some are two pages, or more) 11K buffer rows would require an 88M (11000*8000) buffer.

    If you're happy and you know it vote and mark.

     
  • Thursday, January 10, 2013 3:13 AM
     
     
    Sign In to Vote
    0
    Double check again one source data - If all the data in source is perfect.
     
  • Monday, January 14, 2013 10:14 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Hi guest369,

    Based on the error message, the issue might occur if there is not enough memory available or the buffer size is too small.
    Here, I suggest that you refer to the following steps:

    1. Reduce the SQL Server engine max memory by running the sp_configure stored procedure. The following example sets the max server memory option to 4 GB:

      sp_configure 'show advanced options', 1;
  GO
  RECONFIGURE;
  GO
  sp_configure 'max server memory', 4096;
  GO
  RECONFIGURE;
  GO
    For more information, please see:
    Server Memory Server Configuration Options

    2. Adjust the buffer size by increasing the DefaultBufferMaxSize and DefaultBufferMaxRows properties of Data Flow tasks. For more information about it, please see:
    Data Flow Performance Features

    Hope this helps.

    Mike Yin
    TechNet Community Support