ODBC Destination

Overview

ODBC Destination Component is SSIS Data Flow Component for loading data into ODBC-compatible database table. The component can use either the standard ODBC insert interface or the optimized bulk-load engine. The component has been successfully tested with the following databases:

Database Details
SQL Server Support for regular and bulk-load insert.
Oracle Support for regular and bulk-load insert. For faster alternative check Oracle Destination.
DB2 Support for regular insert only. There is an issue with DB2 bulk support. You can find more details here. For faster alternative check DB2 Destination.
MySQL Support for regular and bulk-load insert. The bulk-load is 30x faster compared to regular ODBC. To use download MySQL Connector/ODBC.
Informix Support for regular and bulk-load insert. The bulk-load is 3x faster compared to OLE DB destination. For faster alternative check Informix Destination.
PostgreSQL (EnterpriseDB) Support for regular and bulk-load insert. To use download PostgreSQL ODBC driver.
Microsoft Access Support for regular and bulk-load insert. The bulk-load is 5x and more faster compared to OLE DB destination.
Sybase Support for regular insert only. You have to use ODBC version 15.05.00.1000. Decimal or numeric field types are not working. We have sent the following incident cases to Sybase: 11553913, 11553762, 11553757.

The configuration options are provided in the standard data flow destination component dialog.

Demonstration

Setup

Use the options below to setup the component.

Options

IDbConnection

Select an existing ODBC connection manager.

Related Topics: ODBC Connection Manager

DestinationTable

Specify the destination database table where the data is loaded.

BatchSize

Specify the number of rows to be sent as a batch. If the size is set to 1, the component uses regular insert.

Timeout

Specify the number of seconds allowed for the bulk copy operation to complete before it is aborted.

ErrorRowDisposition (ODBC Destination Input)

Specify how to handle rows with errors.

How to modify destination table dynamically at runtime?

The destination table is available in the data flow properties list. Follow these steps to setup expression:

  • Right-click on data flow canvas and select Properties menu.
  • Scroll-down and find property named like [ODBC Destination].[DestinationTable]. This is the property containing the destination table.
  • Scroll-down and find Expressions property. Setup expression to modify the table dynamically.

Samples

  • To test the performance of ODBC Destination against the standard OLEDB Destination, check this package. You have to create TEST_TABLE table with following columns:
    • COL1 INTEGER
    • COL2 TIMESTAMP
    • COL3 FLOAT
    • COL4 NVARCHAR2 (250)
    • COL5 NVARCHAR2 (250)
    • COL6 DECIMAL (16)

    NumberOfRows variable controls the amount of inserted rows.