How will you calculate the Buffersize and How many Buffers are used to insert records in SSIS

              In SSIS, the dataflow engine uses a buffer-oriented architecture to efficiently handle datasets in memory. It manipulates data as it is transferred from source to destination.

The default buffer size in SSIS is 10,485,760 Bytes (10 MB Approx) &

Default Buffer Max Rows is 10,000 (Ten Thousand) only.

DataFlow Task (right click) --> Properties

                In the above diagram its shows clearly and which place the Buffersize and Buffermaxrows are allocated in the SSIS Package.

              The Number of Buffers created is depends on how many rows fit into the buffer, and how many rows fit into a buffer depends on a few other factors like

• Row size and all columns information from the input records

• Default Buffer Size property in the dataflow task [MaxBufferSize (100MB) and MinBufferSize (64 KB)]

• DefaultBufferMaxRows Property in the dataflow task [Default value is 10,000].

For gives Better Buffer performance you can do few things.

• Remove the unwanted columns from the source and set the datatype of each column properly.

• If your system has sufficient memory then increases the buffer size in dataflow task.

For Example:

We have 1 cr records from one database to another database by using SSIS package.

By using DefaultBufferMaxRows that means 10,000

It’s completed on 1.03.757 (1 min 3 seconds)

Change the value :

DefaultBufferMaxRows – 500000 (5 lakh’s)

It’s completed on 25.178 ( 25 seconds)

Let me know what you think about this Article.