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.

Note

If you attempt to use the SQL Server destination to bulk load data into a remote SQL Server database, you may see an error message similar to the following: "An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2 (The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.""

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.

Important

Users who execute packages that include the SQL Server destination require the "Create global objects" permission. You can grant this permission to users by using the Local Security Policy tool opened from the Administrative Tools menu. If you receive an error message when executing a package that uses the SQL Server destination, make sure that the account running the package has the "Create global objects" permission.

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

Release	History
14 April 2006	New content: Added information about configuring the size of the batch to commit on an insert.
5 December 2005	New content: Clarified scenarios for use of OLE DB destination vs. SQL Server destination.