Bulk Insert Task Properties (Options Tab)

DTS Designer

DTS Designer Help

Bulk Insert Task Properties (Options Tab)

Use this tab to specify properties with which to organize your file.

Check constraints

Ensure that any constraints on the destination table are checked during the bulk copy operation. By default, constraints are ignored.

Enable identity insert

Specify that there are values in the data file for an identity column.

Sorted data

Indicate to the task that the data in the data file has been sorted on the specified column. The column name that you supply must be a valid column in the destination table.

Keep NULL values

Specify that any columns containing a null value should be retained as null values, even if a default value was specified for that column in the destination table.

Lock entire table

Specify a table-level lock on the destination table for the duration of the BULK INSERT operation.

Code page

Indicate to the task that your data file has char, varchar, or text columns with character values greater than 127 or less than 32, and then choose the appropriate code page value so that the data can be parsed correctly.

Data file type

Specify the type of data in the data file.

Insert batch size

Specify the number of rows in a batch. The default is the entire data file.

The following values for the Batch size property have these effects:

If you set Batch size to zero, the data is loaded in a single batch. The first row that fails will cause the entire load to be canceled, and the step fails.

If you set Batch size to one, the data is loaded a row at a time. Each row that fails is counted as one row failure. Previously loaded rows are either committed or, if the step has joined the package transaction, provisionally retained in the transaction, subject to later commit or rollback.

If you set Batch size to a value greater than one, the data is loaded one batch at a time. Any row that fails in a batch fails that entire batch; loading stops, and the step fails. Rows in previously loaded batches are either committed or, if the step has joined the package transaction, provisionally retained in the transaction, subject to later commit or rollback.

Only copy selected rows

Specify whether a continuous range of rows should be copied in a range.

Starting with row
Specify the first row from which to start copying.
Stopping at row
Specify the last row to copy. 

See Also

BULK INSERT

Bulk Insert Task

BulkInsertTask Object