Use the defaultBufferSize setting to improve the throughput of SSIS package.
Key things you need to know:
- Each component in an SSIS data flow is assigned a memory buffer. The default is about 10MB.
- When your source is a wide table (i.e. has many or large columns), each row takes up a large amount of memory.
- If you have a wide source table then the throughput is constrained to the number of rows that SSIS can fit into the buffer.
- If you increase the buffer size you improve throughput.
In SSIS packages generated by Dimodelo Architect ( A Data Warehouse Automation tool for the Microsoft SQL Server platform), DefaultBufferMaxRows is set 30000 and DefaultBufferSize to 31457380.
To change the defaults on each SSIS package Dimodelo Architect generates, do the following:
- Advanced tab in the Extract dialog of the Staging table editor
- or ETL dialog of the Dimension/Fact editors
Add the tags shown in the image below (click to expand)
In this example the DefaultBufferMaxRows is set 30000 (same as default for Dimodelo generated packages), and the DefaultBufferSize is increased to 94372140 (about 90MB).
Reference: Data Flow Performance