Are SSIS properties DefaultBufferMaxRows and DefaultBufferSize relevant for text file imports?

Question

We use SSIS to import .csv files to SQL Server. DefaultBufferMaxRows and DefaultBufferSize are set to their defaults (10k rows, 10MB)

DefaultBufferMaxRows and DefaultBufferSize can impact loading from a database table because you can only read the needed records. But how can the properties DefaultBufferMaxRows and DefaultBufferSize impact the load performance for .csv files since the files must be first opened and held in memory?

Answer 1

Are SSIS properties DefaultBufferMaxRows and DefaultBufferSize relevant for text file imports?

Of course, yes! These properties are related to the data pipeline, not the source component.

As defined in Wikipedia:

Data buffer is a region of memory used to temporarily store data while it is being moved from one place to another

The DefaultBufferSize property indicates the default size of the data flow task's buffer. The default value is set to 10485760 bytes = 10 megabytes. The DefaultBufferMaxRows property indicates the maximum number of rows that can fit within the data buffer. The default value is set to 10000 rows.

If you are looking for more information, you can refer to the following article:

• SSIS Bad Habits: Decreasing SSIS Package Performance