ODBC Destination

  • Article

The ODBC destination bulk loads data into ODBC-supported database tables. The ODBC destination uses an ODBC connection manager to connect to the data source.

An ODBC 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 may 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, columns of different types can be mapped, however if the input data is not compatible for the destination column type, an error occurs at runtime. Depending on the error behavior setting, the error will be ignored, cause a failure, or the row is sent to the error output.

The ODBC destination has one regular output and one error output.

Load Options

The ODBC destination can use one of two access load modules. You set the mode in the ODBC Source Editor (Connection Manager Page). The two modes are:

  • Batch: In this mode the ODBC destination attempts to use the most efficient insertion method based on the perceived ODBC provider capabilities. For most modern ODBC providers, this would mean preparing an INSERT statement with parameters and then using a row-wise array parameter binding (where the array size is controlled by the BatchSize property). If you select Batch and the provider does not support this method, the ODBC destination automatically switches to the Row-by-row mode.

  • Row-by-row: In this mode, the ODBC destination prepares an INSERT statement with parameters and uses SQL Execute to insert rows one at a time.

Error Handling

The ODBC destination has an error output. The component error output includes the following output columns:

  • Error Code: The number that corresponds to the current error. See the documentation for your source database for a list of errors. For a list of SSIS error codes, see the SSIS Error Code and Message Reference.

  • Error Column: The source column causing the error (for conversion errors).

  • The standard output data columns.

Depending on the error behavior setting, the ODBC destination supports returning errors (data conversion, truncation) that occur during the extraction process in the error output. For more information, see ODBC Source Editor (Error Output Page).

Parallelism

There is no limitation on the number of ODBC destination components that can run in parallel against the same table or different tables, on the same machine or on different machines (other than normal global session limits).

However, limitations of the ODBC provider being used may restrict the number of concurrent connections through the provider. These limitations limit the number of supported parallel instances possible for the ODBC destination. The SSIS developer must be aware of the limitations of any ODBC provider being used and take them into consideration when building SSIS packages.

You must also be aware that concurrently loading into the same table may reduce performance because of standard record locking. This depends on the data being loaded and on the table organization.

Troubleshooting the ODBC Destination

You can log the calls that the ODBC source makes to external data providers. You can use this logging capability to troubleshoot the saving of data to external data sources that the ODBC destination performs. To log the calls that the ODBC destination makes to external data providers, enable the ODBC driver manager trace. For more information, see the Microsoft documentation on How To Generate an ODBC Trace with ODBC the Data Source Administrator.

Configuring the ODBC Destination

You can configure the ODBC destination programatically or through the SSIS Designer

For more information, see one of the following topics:

The Advanced Editor dialog box contains the properties that can be set programmatically.

To open the Advanced Editor dialog box:

  • In the Data Flow screen of your SQL Server 2012 Integration Services (SSIS) project, right click the ODBC destination and select Show Advanced Editor.

For more information about the properties that you can set in the Advanced Editor dialog box, see ODBC Destination Custom Properties.

In This Section