OLE DB Destination

  • Article

The OLE DB destination loads data into a variety of OLE DB-compliant databases using a database table or view or an SQL command. For example, the OLE DB source can load data into tables in Microsoft Office Access and SQL Server 2005 databases.

Note

To load data to a data source that uses Microsoft Office Excel 2007, use an OLE DB destination. You cannot use an Excel destination to load data to an Excel 2007 data source. For more information, see OLE DB Connection Manager. To load data to a data source that uses Microsoft Office Excel 2003 or earlier, use an Excel destination. For more information, see Excel Destination.

The OLE DB destination provides five different data access modes for loading data:

  • A table or view. You can specify an existing table or view, or you create a new table.
  • A table or view using fast-load options. You can specify an existing table or create a new table.
  • A table or view specified in a variable.
  • A table or view specified in a variable using fast-load options.
  • The results of an SQL statement.

Note

The OLE DB destination does not support parameters. If you have to execute a parameterized INSERT statement, use the OLE DB Command transformation. For more information, see OLE DB Command Transformation.

When the OLE DB destination loads data that uses a double-byte character set (DBCS), the data may be corrupted if the data access mode does not use the fast load option and if the OLE DB connection manager uses the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) To ensure the integrity of DBCS data you should configure the OLE DB connection manager to use the SQL Native Client, or use one of the fast-load access modes: Table or view - fast load or Table name or view name variable - fast load. Both options are available from the OLE DB Destination Editor dialog box. When programming the SSIS object model, you should set the AccessMode property to OpenRowset Using FastLoad, or OpenRowset Using FastLoad From Variable.

Note

If you use the OLE DB Destination Editor dialog box in SSIS Designer to create the destination table into which the OLE DB destination inserts data, you may have to select the newly created table manually. The need for manual selection occurs when an OLE DB provider, such as the OLE DB provider for DB2, automatically adds schema identifiers to the table name.

Note

The CREATE TABLE statement that the OLE DB Destination Editor dialog generates may require modification depending on the destination type. For example, some destinations do not support the data types that the CREATE TABLE statement uses.

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, to make data sources and data source views available to the OLE DB destination. For more information, see Data Source (SSIS) and Data Source View (SSIS).

An OLE DB destination includes mappings between input columns and columns in the destination data source. You do not have to map input columns to all destination columns, but depending on the properties of the destination columns, errors can occur if no input columns are mapped to the destination columns. For example, if a destination column does not allow null values, an input column must be mapped to that column. In addition, the data types of mapped columns must be compatible. For example, you cannot map an input column with a string data type to a destination column with a numeric data type.

The OLE DB destination has one regular input and one error output.

Fast Load Options

If the OLE DB destination uses a fast-load data access mode, you can specify the following fast load options in the user interface, OLE DB Destination Editor, for the destination:

  • Keep identity values from the imported data file or use unique values assigned by SQL Server.
  • Retain a null value during the bulk load operation.
  • Check 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.
  • Specify the number of rows in the batch and the commit size.

Some fast load options are stored in specific properties of the OLE DB destination. For example, FastLoadKeepIdentity specifies whether to keep identify values, FastLoadKeepNulls specifies whether to keep null values, and FastLoadMaxInsertCommitSize specifies the number of rows to commit as a batch. Other fast load options are stored in a comma-separated list in the FastLoadOptions property. If the OLE DB destination uses all the fast load options that are stored in FastLoadOptions and listed in the OLE DB Destination Editor dialog box, the value of the property is set to TABLOCK, CHECK_CONSTRAINTS, ROWS_PER_BATCH=1000. The value 1000 indicates that the destination is configured to use batches of 1000 rows.

Note

Any constraint failure at the destination causes the entire batch of rows defined by FastLoadMaxInsertCommitSize to fail.

In addition to the fast load options exposed in the OLE DB Destination Editor dialog box, you can configure the OLE DB destination to use the following bulk load options by typing the options in FastLoadOptions property in the Advanced Editor dialog box.

Fast load option

Description

KILOBYTES_PER_BATCH

Specifies the size in kilobytes to insert. The option has the form KILOBYTES_PER_BATCH = <positive integer value**>**.

FIRE_TRIGGERS

Specifies whether triggers fire on the insert table. The option has the form FIRE_TRIGGERS. The presence of the option indicates that triggers fire.

ORDER

Specifies how the input data is sorted. The option has the form ORDER <column name> ASC|DESC. Any number of columns may be listed and it is optional to include the sort order. If sort order is omitted, the insert operation assumes the data is unsorted.

ms141237.note(en-US,SQL.90).gifNote:

Performance can be improved if the data being loaded is sorted according to the clustered index on the table.

The Transact-SQL keywords are traditionally typed using uppercase letters, but the keywords are not case sensitive.

To learn more about fast load options, see BULK INSERT (Transact-SQL).

Troubleshooting the OLE DB Destination

Starting in Microsoft SQL Server 2005 Service Pack 2 (SP2), you are able to log the calls that the OLE DB destination makes to external data providers. You can use this new logging capability to troubleshoot the saving of data to external data sources that the OLE DB destination performs. To log the calls that the OLE DB destination makes to an external data provider, enable package logging and select the Diagnostic event at the package level. For more information, see Troubleshooting Package Execution.

Configuring the OLE DB Destination

You can set properties through SSIS Designer or programmatically.

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

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:

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

See Also

Concepts

OLE DB Source
Integration Services Variables
Creating Package Data Flow

Other Resources

Integration Services Destinations

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content:
  • Added a note about the use of Excel 2007 data sources.
  • Added information about how SQL Server 2005 SP2 includes new logging messages that enable users to troubleshoot the calls that the destination makes to external data providers.

17 July 2006
Changed content:
  • Noted that constraint failures at the destination will cause the batch to fail.
  • Fixed typo in property name.

5 December 2005
Changed content:
  • Provided more information about fast load options.
  • Added note about the need to modify the definition of the table that is generated.