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:
|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.|
Use the parameters below to set up the component.
Select an existing ODBC connection manager.
Related Topics: ODBC Connection Manager
Specify the destination database table where the data is loaded.
Specify the number of rows to be sent as a batch. If the size is set to 1, the component uses regular insert.
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.
- 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.