1

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Rory
  • 959
  • 10
  • 22
  • SSIS will not load the complete csv file in memory before it starts processing rows. – Wouter Dec 16 '22 at 09:47
  • Thanks @Wouter what happens on the file server? Does the file remain open and ssis just reads from it as though it were a db source? Or is the file opened and closed and reopened? – Rory Dec 16 '22 at 11:51

1 Answers1

0

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124