Bulk Insert Task

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. For example, suppose your company stores its million-row product list on a mainframe system, but the company's e-commerce system uses SQL Server to populate Web pages. You must update the SQL Server product table nightly with the master product list from the mainframe. To update the table, you save the product list in a tab-delimited format and use the Bulk Insert task to copy the data directly into the SQL Server table.

To ensure high-speed data copying, transformations cannot be performed on the data while it is moving from the source file to the table or view.

Usage Considerations

Before you use the Bulk Insert task, consider the following:

• The Bulk Insert task can transfer data only from a text file into a SQL Server table or view. To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view.

• The destination must be a table or view in a SQL Server database. If the destination table or view already contains data, the new data is appended to the existing data when the Bulk Insert task runs. If you want to replace the data, run an Execute SQL task that runs a DELETE or TRUNCATE statement before you run the Bulk Insert task. For more information, see Execute SQL Task.

• You can use a format file in the Bulk Insert task object. If you have a format file that was created by the bcp utility, you can specify its path in the Bulk Insert task. The Bulk Insert task supports both XML and nonXML format files. For more information about format files, see Format Files for Importing or Exporting Data (SQL Server).

• Only members of the sysadmin fixed server role can run a package that contains a Bulk Insert task.

Bulk Insert Task with Transactions

If a batch size is not set, the complete bulk copy operation is treated as one transaction. A batch size of 0 indicates that the data is inserted in one batch. If a batch size is set, each batch represents a transaction that is committed when the batch finishes running.

The behavior of the Bulk Insert task, as it relates to transactions, depends on whether the task joins the package transaction. If the Bulk Insert task does not join the package transaction, each error-free batch is committed as a unit before the next batch is tried. If the Bulk Insert task joins the package transaction, error-free batches remain in the transaction at the conclusion of the task. These batches are subject to the commit or rollback operation of the package.

A failure in the Bulk Insert task does not automatically roll back successfully loaded batches; similarly, if the task succeeds, batches are not automatically committed. Commit and rollback operations occur only in response to package and workflow property settings.

Source and Destination

When you specify the location of the text source file, consider the following:

• The server must have permission to access both the file and the destination database.

• The server runs the Bulk Insert task. Therefore, any format file that the task uses must be located on the server.

• The source file that the Bulk Insert task loads can be on the same server as the SQL Server database into which data is inserted, or on a remote server. If the file is on a remote server, you must specify the file name using the Universal Naming Convention (UNC) name in the path.

Performance Optimization

To optimize performance, consider the following:

• If the text file is located on the same computer as the SQL Server database into which data is inserted, the copy operation occurs at an even faster rate because the data is not moved over the network.

• The Bulk Insert task does not log error-causing rows. If you must capture this information, use the error outputs of data flow components to capture error-causing rows in an exception file.

Custom Log Entries Available on the Bulk Insert Task

The following table lists the custom log entries for the Bulk Insert task. For more information, see Integration Services (SSIS) Logging and Custom Messages for Logging.

Bulk Insert Task Configuration

You can configure the Bulk Insert task in the following ways:

• Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted. The Bulk Insert task supports only OLE DB connections for the destination database.

• Specify the File or Flat File connection manager to access the source file. The Bulk Insert task uses the connection manager only for the location of the source file. The task ignores other options that you select in the connection manager editor.

• Define the format that is used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file.

• Specify actions to perform on the destination table or view when the task inserts the data. The options include whether to check constraints, enable identity inserts, keep nulls, fire triggers, or lock the table.

• Provide information about the batch of data to insert, such as the batch size, the first and last row from the file to insert, the number of insert errors that can occur before the task stops inserting rows, and the names of the columns that will be sorted.

If the Bulk Insert task uses a Flat File connection manager to access the source file, the task does not use the format specified in the Flat File connection manager. Instead, the Bulk Insert task uses either the format specified in a format file, or the values of the RowDelimiter and ColumnDelimiter properties of the task.

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in SSIS Designer, click one of the following topics:

• Bulk Insert Task Editor (General Page)

• Bulk Insert Task Editor (Connection Page)

• Bulk Insert Task Editor (Options Page)

• Expressions Page

For more information about how to setthese properties in SSIS Designer, click the following topic:

• Set the Properties of a Task or Container

Programmatic Configuration of the Bulk Insert Task

For more information about programmatically setting these properties, click the following topic:

• BulkInsertTask

Related Tasks

Set the Properties of a Task or Container

Related Content

• Technical article, You may get "Unable to prepare the SSIS bulk insert for data insertion" error on UAC enabled systems, on support.microsoft.com.

• Technical article, The Data Loading Performance Guide, on msdn.microsoft.com.

• Technical article, Using SQL Server Integration Services to Bulk Load Data, on simple-talk.com.