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.
  
• Only members of the sysadmin fixed server role can run a package that contains a Bulk Insert task.
  

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. For more information, see Implicit Transactions.

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.

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.
  

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.
  

The following table lists the custom log entries for the Bulk Insert task. For more information, see Implementing Logging in Packages and Custom Messages for Logging.

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:

• How to: Set the Properties of a Task or Container
  

Configuring the Bulk Insert Task Programmatically

Tasks

Concepts

Help and Information