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 parameters are provided in the standard Data Flow Component dialogs.
Sample package
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.
Demonstration
Parameters
Configuration
Use the parameters below to configure 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.
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. Specify how to handle rows with errors.
Knowledge Base
- Where can I find the documentation for the ODBC Destination?
- An error occurred with the following error message: "'0000-00-00 00:00:00' default is invalid for column type TYPE_TIMESTAMP".
- Error Message: The ConnectionString property has not been initialized.
- ODBC Destination: How to modify the ODBC Destination table dynamically at runtime
What's New
- Fixed: Failed with error "SQLBulkOperations(SQL_ADD) ERROR:Invalid input syntax for type numeric: """ when trying to insert NULL value into column of type NUMERIC(28,10) (Thank you, Gustavo).
- Fixed: Component didn't process the 3-digit milliseconds of timestamp columns properly (Thank you, Irawan).
- Fixed: The bulk initialization statement was needlessly retrieving rows, slowing the process (Thank you, Irawan).
- Fixed: Failed to process columns with uuid data type.
- Fixed: Component didn't process the milliseconds of timestamp columns properly (Thank you, Irawan).
- New: More information reported in case of errors.
- Fixed: Component inserted mangled text when using MySQL database as destination (Thank you, Lokeswar).
- Fixed: Component failed to insert data into a table where column names contained space (Thank you, Valeriu).
- Fixed: ODBC Destination component failed to process in 64bit mode.
- New: A new parameter BatchMode. The choices are: BulkOperations, ArrayParameter.
- Fixed: Component failed to load properly DECIMAL type columns in Microsoft Access (Thank you, Geert).
- Fixed: Failed to properly close Microsoft Access database, when no input data was provided.
- Fixed: Component truncated long text columns containing more than 65535 characters (Thank you, Doug).
- Fixed: Failed with "String cannot be of zero length. Parameter name: oldValue" error when destination database is IBM Informix Dynamic Server.
- Fixed: Component didn't process decimal columns properly in MySQL database (Thank you, Romeo).
- Fixed: Component failed with "Unable to cast object of type 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn' to type ..." error when input column was of type DT_BYTES, DT_IMAGE, DT_TEXT or DT_NTEXT (Thank you, Allen).
- Fixed: Failed with "Unhandled type." error when processing FLOAT columns (Thank you, Edward).
- Fixed: Component failed occasionally with "Specified cast is not valid." error when processing INTEGER columns in MySQL.
- Fixed: Failed to load to table with long text columns.
- New: Introduced component.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.