We help IT Professionals succeed at work.

SSIS custom data source component - buffer overflow handling

Developing a SQL Server Integration Services Custom Data Source Data Flow Component.

SSIS has a maximum buffer size and row limit to "chunk" work based on buffer size.

This requires a source component to recognize when the buffer is full, and then suspend until another buffer is available and resume loading from the source to the new buffer.

What I don't see covered anywhere is:
 - how to effectively deal with a full buffer
    > calculate the size, count rows and ensure closing the buffer before overflow (except that I can't see how to get the max size or max rows from the parent data flow task)
    > wait for a buffer overflow exception and rewind to the last row/bytes written to resume with a new buffer.
 - how to store the "bytes/rows to skip" information for the component to pick up the next time it's invoked by SSIS.
Watch Question

Kent DyerIT Security Analyst Senior

When you run into this situation, you can do one of two things..  Do some brute-force tweaking which you have already done, or you can place with an ODBC Connector to see what you can do with this as well..


Found at least a partial answer in books online:

There is no way to determine when a batch of rows has been sent to the next component because the movement of rows by the data flow task is transparent to the component developer, and the RowCount property is always zero on output buffers. When a source component is finished adding rows to its output buffer, it notifies the data flow task by calling the SetEndOfRowset method of the PipelineBuffer, and the remaining rows in the buffer are passed to the next component.

However, that doesn't answer the problem of SSIS running out of buffers... what exceptions can pipelineBuffer.addRow() throw?


'cause no one else provided an answer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.