Batch Size (Model Object)

Batch Size (BatchSize) passes the approximate number of rows of a source file to the ROWS_PER_BATCH argument of OPENROWSET. SQL Server uses it to plan the bulk load of the file into the Stage.

The property exists only in the Microsoft SQL Server - Stage Files generator.

Where to set it

Set Batch Size when you edit a model object of type Source. It is a text field that takes a whole number.

Values and default

• 0 (default): the loader passes ROWS_PER_BATCH = 0.
• Another number: the loader passes that number.

Every Stage that reads the Source passes the same value. The meaning of the argument is described in the Microsoft documentation OPENROWSET BULK (Transact-SQL).

Example

With Batch Size 1000 on the Source SALESTRANSACTION, the raw data loader of the Stage load_incremental reads the file with:

FROM OPENROWSET(
     BULK ''' + REPLACE(@SourceDirectory + @SourceFile, ...) + N'''
    ,FORMATFILE = ''' + ... + N'load_incremental.fmt''
    ,FORMAT = ''CSV''
    ,CODEPAGE = ''65001''
    ,FIRSTROW = 2
    ,ROWS_PER_BATCH = 1000
    ,ERRORFILE = ''' + ... + N'''
    ,MAXERRORS = 0

The other arguments come from the file properties of the Source, for example the code page and the first data row.

Related articles

• See also: Model a Stage File Solution, Microsoft SQL Server - Stage Files