CODEWITHSUNDEEP
sql-serverssisinsertbulkinsert
Ric_R
Ric_R

Reputation: 155

SSIS - Insert Bulk How many Rows per batch to choose?

I'm using bulk insert in SSIS to fill a table with approx 10M rows in SQL Server. I've googled a lot about it, and still could not find an answer for it.

How many Rows per batch should I choose? How many Maximum insert commit size should I choose?

It would be great to receive an explanation about each one.

I've tested it and changed the Rows per batch. The best result I've received so far is for 5M.

Appreciate your help!

Upvotes: 0

Views: 5875

Answers (2)

vinay koul
vinay koul

Reputation: 346

ROWS_PER_BATCH should be set to your estimate of the "total number of rows in source" as this is then used by the SQL server to optimize the query plan for loading this data in a single operation.

So, based on my interpretation the suggested settings should be as follows:

If "Maximum insert commit size" is specified then don't bother setting "Rows Per Batch" as it will be ignored anyway

If "Maximum insert commit size" is not specified i.e. left as 0, then set "Rows Per Batch" to yout best estimate of the "total number of rows" that will loaded in order to enable SQL server to select the most efficient way to perform the operation.

Upvotes: 1

LONG
LONG

Reputation: 4610

For BatchSize properties, I think all data will be pushed to one batch if you set it to 0

Upvotes: 0

Related Questions