ODBC Destination

SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

All Downloads ----- Questions?

Overview

The ODBC Destination Component is an SSIS Data Flow Component for loading data into an 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 a faster alternative see 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.
Vertica Support for regular and bulk-load insert. The bulk-load is 400x faster compared to regular insert. Use BatchMode=ArrayParameter.
Informix Support for regular and bulk-load insert. The bulk-load is 3x faster compared to OLE DB destination. For a faster alternative, see Informix Destination.
PostgreSQL (EnterpriseDB) Support for regular and bulk-load insert. To use, download the 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 parameters below to set up the component.

Parameters

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.

BatchMode (1.6 SR-2)

Specify batch mode. This parameter has the options listed in the following table.

Option Description
BulkOperations Perform bulk-load based on SQLBulkOperations function.
ArrayParameter Perform bulk-load based on array parameters.
ErrorRowDisposition (ODBC Destination Input)

Specify how to handle rows with errors.

How to modify the destination table dynamically at runtime

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

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

Samples

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

    The NumberOfRows variable controls the number of rows inserted.