SSIS custom data source component - buffer overflow handling

Posted on 2012-08-13
Last Modified: 2013-09-17
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.
Question by:NJThomson
    LVL 17

    Expert Comment

    by:Kent Dyer
    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..



    Accepted Solution

    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?

    Author Closing Comment

    'cause no one else provided an answer

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now