Can someone provide further insight into how the SSIS buffer manager calculates the 'Estimated Row Size'?
I know that metadata of source components is examined and that the maximum size of variable-length strings is used. Am I right in thinking that it also depends on the Transformations used in the buffer's execution tree?
I ask because the Derived Column Transformation is synchronous (no memory is copied as buffers are reused). This means that any Derived Column Transforms in the same execution tree affect the number of rows the buffer can handle. This obviously extends to other Transformations and is not limited to synchronous ones.
I feel I'm answering my own question here but would really appreciate a nod from someone more familiar with SSIS internals.
It's also worth pointing out as every online article I've consulted on this cites 'the metadata [about your] source data' as the basis for the Estimated Row Size. I've yet to find one that mentions Transformations!
asked May 24 '12 at 04:37 PM in Default
I do not think you would find any mentioning of the Transformations to calculate the Estimated Row Size ;) You are mixing it up.
Estimated Row size is used to calculate the size of the buffer.
Asynchronous and Synchronous transformations defines whether additional buffers would be needed or the incoming input buffers can be utilized.
I have kept it simple, so you do not get more confused.
answered May 28 '12 at 12:27 PM