SQL:Calculating Ideal Default Buffer Max Row and Default Buffer Size in SSIS

Calculating Default Buffer Max Row  and Default Buffer Size

Calculate the Table Size using

Exec sp_spaceused  ‘tablename’

This should give you table size occupied in KB and the no of Records in the table





In case you are including the error records and derived columns  then you need to include that in to account during calculation.
Calculate using the derived column datatype and size , and include the same in the row size.

Now the size of each row in Bytes= (total size of table * 1024)/No of Rows  

Defualt Buffer size can be extended upto 10,485,760 Bytes (10 GB Approx)

No of Rows for 10 GB Buffer Size = 10,485,760/Size of Each Row