Buffer Settings

Data buffers are managed at the data flow task object level. These settings can be changed in the properties windows by selecting a data flow task in the package designer. Although it can be said that a buffer can be created to manage a new data flow pipeline, this might in fact create multiple buffers. Each physical buffer holds a defined number of rows. A data flow for a large number of rows will overflow to new buffers when the row count exceeds the maximum number of buffer rows or the buffer size exceeds the defined buffer size property settings, whichever threshold is exceeded first.

Blocking operations, such as a sort transformation, will require buffers to contain the entire row set. Non-blocking operations might require enough buffers to manage the flow to and from the transformation but not require the entire row set to sit in memory at one time.

To optimize large result sets, you might set the buffer's maximum number of rows to accommodate anticipated data volume and to work within the server's memory constraints. Transformations that change row value are applied to one buffer at a time. The following properties can be set or adjusted for each data flow task in a package:

• BufferTempStoragePath Buffer contents can be saved to temporary storage space in the file system. This setting is used to specify one or more folders for these temporary files.

• DefaultBufferMaxRows This is the maximum number of rows for each physical buffer allocation. Results larger than this setting will result in multiple buffers.

• DefaultBufferSize This setting is for the size of each buffer in the data flow, in bytes. Any value for this property should be divisible by 1,024 and is typically divided evenly into kilobytes (x 1,024) or megabytes (x 1,048,576). The default setting is 10,485,760 or 10 megabytes.