0

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!

Squashman
  • 13,649
  • 5
  • 27
  • 36
Ric_R
  • 145
  • 2
  • 11
  • Some good answers in this existing question: http://stackoverflow.com/questions/2739320/ssis-2008-rows-per-batch-and-maximum-insert-commit-size#2739416 – jorgesalvador Dec 21 '16 at 16:32

2 Answers2

1

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.

vinay koul
  • 348
  • 1
  • 9
0

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

LONG
  • 4,490
  • 2
  • 17
  • 35
  • But what's the best approach to it? Why to use 0? Thanks – Ric_R Dec 21 '16 at 16:32
  • 1
    there is no such `best approach` but depends on your real case. In short words, when set to 0, all the data will be pushed in one batch, it will be treated as one transaction, if anything fail, the transaction will be rolled back, but for specific number of rows for batch, for example, 100 rows per batch, it will insert 100 rows per transaction, if anything fail during batch 3, the inserted batch 1 and batch 2 will NOT be rolled back only batch 3 and the task stopped. – LONG Dec 21 '16 at 16:44
  • If you need more reference:https://technet.microsoft.com/en-us/library/ms188267(v=sql.105).aspx – LONG Dec 21 '16 at 16:45