Performance Tunning in SSIS Data Flow Task

SSIS Buffer Calculation

First check the data size in the table
sp_spaceused ‘TestDB.dbo.DimCustomer‘

1. Data in Bytes = 69200 * 1024 = 70860800 KB
2. Divide by Row Count = 70860800 / 253444 = 280 Bytes
3. So Row Width = 280 Bytes
4. 100 MB Buffer = 104857600
5. Row Width = 280 Bytes
6 104857600/ 280 = 374492

So Max Buffer Row = 374492 should be set which will take care to even 3,000,000 rows. The Buffer Row Count will change only when the Data type changes

Eg. Varchar(100) has been increased to Varchar(500), Then we need to use the formula to recalculate.