SQL Server Destination

The SQL Server destination connects to a local SQL Server database and bulk loads data into SQL Server tables and views. You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination. For more information, see OLE DB Destination.

The SQL Server destination offers the same high-speed insertion of data into SQL Server that the Bulk Insert task provides; however, by using the SQL Server destination, a package can apply transformations to column data before the data is loaded into SQL Server.

For loading data into SQL Server, you should consider using the SQL Server destination instead of the OLE DB destination.

You can configure the SQL Server destination in the following ways:

• Specify the table or view into which to bulk load the data.
• Customize the bulk load operation by specifying options such as whether to check constraints.
• Specify whether all rows commit in one batch or set the maximum number of rows to commit as a batch.
• Specify a time-out for the bulk load operation.

This destination uses an OLE DB connection manager to connect to a data source, and the connection manager specifies the OLE DB provider to use. For more information, see OLE DB Connection Manager.

An Integration Services project also provides the data source object from which you can create an OLE DB connection manager. This makes data sources and data source views available to the SQL Server destination. For more information, see Data Source (SSIS) and Data Source View (SSIS).

The SQL Server destination has one input. It does not support an error output.

Bulk Insert Options

If the SQL Server destination uses a fast-load data access mode, you can specify the following fast load options:

• Retain identity values from the imported data file, or use unique values assigned by SQL Server.
• Retain null values during the bulk load operation.
• Verify constraints on the target table or view during the bulk import operation.
• Acquire a table-level lock for the duration of the bulk load operation.
• Execute insert triggers defined on the destination table during the bulk load operation.
• Specify the number of the first row in the input to load during the bulk insert operation.
• Specify the number of the last row in the input to load during the bulk insert operation.
• Specify the maximum number of errors allowed before the bulk load operation is canceled. Each row that cannot be imported is counted as one error.
• Specify the columns in the input that contain sorted data.

For more information about bulk load options, see BULK INSERT (Transact-SQL).

Improving Performance of the SQL Server Destination

To improve the performance of a bulk insert and the access to table data during the bulk insert operation, you should change the default options as follows:

• Do not verify constraints on the target table or view during the bulk import operation. For more information, see CHECK Constraints.
• Do not execute insert triggers defined on the destination table during the bulk load operation. For more information, see DML Triggers.
• Do not apply a lock to the table. That way, the table remains available to other users and applications during the bulk insert operation.

Configuring the SQL Server Destination

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in the SQL Server Destination Editor dialog box, click one of the following topics:

• SQL Destination Editor (Connection Manager Page)
• SQL Destination Editor (Mappings Page)
• SQL Destination Editor (Advanced Page)

The Advanced Editor dialog box reflects the properties that can be set programmatically. For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics:

• Common Properties
• Destination Custom Properties

For more information about how to set properties, click one of the following topics:

• How to: Bulk Load Data Using the SQL Server Destination
• How to: Set the Properties of a Data Flow Component Using a Component Editor
• How to: Set the Properties of a Data Flow Component in the Properties Window
• How to: Set the Properties of a Data Flow Component Using the Advanced Editor

See Also

Concepts

Creating Package Data Flow

Other Resources

Integration Services Destinations

Help and Information

Getting SQL Server 2005 Assistance

Change History