SSIS package keep failing buffer size not working

Irakli Mikadze 21 Reputation points
2020-12-15T11:55:25.143+00:00

So i have SSIS package with For Loop Container witch is infinit. in for loop container i have data flow
i am executing it in Sql server and after many succes i get error

DataFlow Task Error : A buffer failed while allocating

i tried buffer size 25 mb still this error after i tried:

AutoAdjustBufferSize = true

still get this error can you help me out what can i doo ?

i want this package running all time infint so :(

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2020-12-16T08:47:58.923+00:00

    Hi @Irakli Mikadze ,

    May I know what's the number of your whole buffers and how do you set the DefaultBufferSize and DefaultBufferMaxRows?

    Adjust the Sizing of Buffers
    The data flow engine begins the task of sizing its buffers by calculating the estimated size of a single row of data. Then it multiplies the estimated size of a row by the value of DefaultBufferMaxRows to obtain a preliminary working value for the buffer size.

    • If AutoAdjustBufferSize is set to true, the engine data flow engine uses the calculated value as the buffer size, and the value of DefaultBufferSize is ignored.
    • If AutoAdjustBufferSize is set to false, the engine data flow engine uses the following rules to determine the buffer size.
      a.If the result is more than the value of DefaultBufferSize, the engine reduces the number of rows.
      b.If the result is less than the internally-calculated minimum buffer size, the engine increases the number of rows.
      c.If the result falls between the minimum buffer size and the value of DefaultBufferSize, the engine sizes the buffer as close as possible to the estimated row size times the value of DefaultBufferMaxRows.

    Hope the following link will be helpful:

    Data Flow Performance Features

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?