Bulk Insert Task

The Bulk Insert task provides the quickest way to copy large amounts of data into a Microsoft® SQL Server™ table or view. To ensure high-speed data movement, transformations cannot be performed on the data while it is moved from the source file to the table or view.

For example, suppose your company keeps your million-row product list on a mainframe system. Your e-commerce system uses SQL Server 2000 to populate Web pages. You need to update the product table nightly with the master product list from the mainframe. To do this, 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. The table is now refreshed with the updated product data.

Configuring the Bulk Insert Task

The Bulk Insert task encapsulates a Transact-SQL BULK INSERT statement that is run during task execution. In Data Transformation Services (DTS) Designer, you can set parameters for the BULK INSERT statement in the Bulk Insert Properties dialog box. Any BULK INSERT parameters not available graphically are set to their defaults. If you need to set parameters that are not available graphically, you can use the BulkInsertTask object or the bcp utility to set them programmatically.

Using the Bulk Insert Task with Transactions

If a batch size is not set, then an entire bulk copy operation is considered one transaction. If a batch size is set, then each batch constitutes a transaction that is committed when the batch finishes.

The behavior of the Bulk Insert task depends on whether the task is joined into 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 attempted. 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 step or package.

A failure in the Bulk Insert task does not automatically roll back successfully loaded batches; task success does not automatically commit them. Commit and rollback operations happen only in response to package and workflow property settings. For more information, see DTS Transaction Fundamentals.

Usage Considerations

Before using 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 transfer data from a database management system (DBMS), you need to export the data from the source program to a data file and then import the data from the data file into a SQL Server table or view.
The data destination must be a table or view created by SQL Server. If the destination table or view contains data already, the new data will be appended when the Bulk Insert task runs.
You can use a format file in the Bulk Insert task object. If you have a format file created by the bcp utility, you can specify its path in the Bulk Insert task. The path given must be with respect to the server. For more information about format files, see Using Format Files.

Specifying the Source and Destination

When specifying the path of the text source file, consider the following:

When running the Bulk Insert task, only members of the sysadmin fixed server role can execute the package.
Regardless of the location of the file, the server must have permissions to both the file and the destination database.
The server will be running the Bulk Insert task. Therefore, the path given must be with respect to the server.

Optimizing Performance

To optimize performance, consider the following:

If the text file is located on the same computer running the instance of SQL Server, the copy operation occurs at an even faster rate because the data is not moved across the network.
The Bulk Insert task does not log error-causing rows. If you need to capture this information, another task, the Transform Data task, can capture error-causing rows to an exception file. However, writing errors to the log will slow down the data transfer. If speed is your priority, use the Bulk Insert task. If capturing errors is more important, use the Transform Data task.

To add the Bulk Insert task to a DTS package