ODBC Flow Components
Applies to: 
SQL Server
SSIS Integration Runtime in Azure Data Factory
This topic describes the concepts necessary for creating an ODBC data flow using SQL Server 2019 Integration Services (SSIS)
The Connector for Open Database Connectivity (ODBC) for SQL Server 2019 Integration Services (SSIS) helps SSIS developers easily create packages that load and unload data from ODBC-supported databases.
The ODBC Connector is designed to achieve optimal performance when loading data into or unloading data from an ODBC-supported database in the context of SQL Server 2019 Integration Services (SSIS).
Benefits
The ODBC source and ODBC destination for SQL Server 2019 Integration Services (SSIS) provides a competitive edge for SSIS in projects dealing with loading data into or unloading data from ODBC-supported databases.
Both the ODBC source and ODBC destination enable high performance data integration with ODBC-enabled databases. Both components can be configured to work with row-wise parameter array bindings for high-functioning ODBC providers that support this mode of binding and single-row parameter bindings for low-functioning ODBC providers.
Getting Started with the ODBC Source and Destination
Before you can set up packages that use SQL Server 2019 Integration Services (SSIS), you must make sure that the following are available.
The ODBC source and ODBC destination provide an easy way to unload and load data and transfer data from an ODBC-supported source database to an ODBC-supported destination database.
To use the source or destination to load or unload data, open a new SQL Server 2019 Integration Services (SSIS) Project in the SQL Server Data Tools. Then drag the source or destination onto the design surface of the SQL Server Data Tools.
- The ODBC source component reads data from the source ODBC-supported database.
You can connect the ODBC source to any destination or transform component supported by SSIS.
See also:
ODBC Source
ODBC Source Editor (Connection Manager Page)
ODBC Source Editor (Error Output Page)
- The ODBC destination loads data into an ODBC-supported database. You connect the destination to any source or transform component supported by SSIS.
See also:
ODBC Destination
ODBC Destination Editor (Connection Manager Page)
ODBC Destination Editor (Error Output Page)
Operating Scenarios
This section describes some of the main uses for the ODBC source and destination components.
Bulk Copy Data from SQL Server tables to any ODBC-Supported database table
You can use the components to bulk copy data from one or more SQL Server tables to a single ODBC-supported database table.
The following example shows how to create an SSIS Data Flow Task that extracts data from a SQL Server table and loads it into a DB2 table.
Create an SQL Server 2019 Integration Services (SSIS) Project in the SQL Server Data Tools.
Create an OLE DB connection manager that connects to the SQL Server database that contains the data you want to copy.
Create an ODBC connection manager that uses a locally installed DB2 ODBC driver with a DSN pointing to a local or remote DB2 database. This database is where the data from the SQL Server database is loaded.
Drag an OLE DB source to the design surface, then configure the source to get the data from the SQL Server database and table with the data you are going to extract. Use the OLE DB connection manager you created previously.
Drag an ODBC destination to the design surface, connect the source output to the ODBC destination, then configure the destination to load the data into the DB2 table with the data you extract from the SQL Server database. Use the ODBC connection manager you created previously.
Bulk Copy Data from ODBC-supported database tables to any SQL Server table
You can use the components to bulk copy data from one or more ODBC-supported database tables to a single SQL Server database table.
The following example shows how to create an SSIS Data Flow Task that extracts data from a Sybase database table and loads it into a SQL Server database table.
Create an SQL Server 2019 Integration Services (SSIS) Project in the SQL Server Data Tools
Create an ODBC connection manager that uses a locally installed Sybase ODBC driver with a DSN pointing to a local or remote Sybase database. This database is where the data is extracted.
Create an OLE DB connection manager that connects to the SQL Server database where you want to load the data.
Drag an ODBC source to the design surface, then configure the source to get the data from the Sybase table with the data you are going to copy. Use the ODBC connection manager you created previously.
Drag an OLE DB destination to the design surface, connect the source output to the OLE DB destination, then configure the destination to load the data into the SQL Server table with the data you extract from the Sybase database. Use the OLE DB connection manager you created previously.
Supported Data Types
The ODBC Bulk SSIS components support all built-in ODBC data types, including support for large objects (CLOBs and BLOBs).
There is no data type support for extensible C types as described in the ODBC 3.8 specifications.The following table describes which SSIS data types are used for each ODBC SQL type. An SSIS developer can override the default mapping and specify a different SSIS data type for input/output columns without impacting the performance for the required data conversions.
| ODBC SQL Type | SSIS Data Type | Comments |
|---|---|---|
| SQL_BIT | DT_BOOL | |
| SQL_TINYINT | DT_I1 DT_UI1 |
SQL data types are mapped to SSIS unsigned types (DT_UI1, DT_UI2, DT_UI4, DT_UI8) when the ODBC driver sets the UNSIGNED_ATTRIBUTE to SQL_TRUE for that SQL data type. |
| SQL_SMALLINT | DT_I2 DT_UI2 |
SQL data types are mapped to SSIS unsigned types (DT_UI1, DT_UI2, DT_UI4, DT_UI8) when the ODBC driver sets the UNSIGNED_ATTRIBUTE to SQL_TRUE for that SQL data type. |
| SQL_INTEGER | DT_I4 DTUI4 |
SQL data types are mapped to SSIS unsigned types (DT_UI1, DT_UI2, DT_UI4, DT_UI8) when the ODBC driver sets the UNSIGNED_ATTRIBUTE to SQL_TRUE for that SQL data type. |
| SQL_BIGINT | DT_I8 DT_UI8 |
SQL data types are mapped to SSIS unsigned types (DT_UI1, DT_UI2, DT_UI4, DT_UI8) when the ODBC driver sets the UNSIGNED_ATTRIBUTE to SQL_TRUE for that SQL data type. |
| SQL_DOUBLE | DT_R8 | |
| SQL_FLOAT | DT_R8 | |
| SQL_REAL | DT_R4 | |
| SQL_NUMERIC (p,s) | DT_NUMERIC (p,s) | The numeric data type is mapped to DT_NUMERIC when P is greater than or equal to 38 and S is greater than or equal to 0 and S is less than or equal to P. |
| DT_R8 | The numeric data type is mapped to DT_R8 when at least one of the following is true: Precision is greater than 38 Scale is less than zero Scale is greater than 38 Scale is greater than Precision |
|
| DT_CY | The numeric data type is mapped to DT_CY when it is declared as a money data type. | |
| SQL_DECIMAL (p,s) | DT_NUMERIC (p,s) | The decimal data type is mapped to DT_NUMERIC when P is greater than or equal to 38 and S is greater than or equal to 0 and S is less than or equal to P. |
| DT_R8 | The decimal data type is mapped to DT_R8 when at least one of the following is true: Precision is greater than 38 Scale is less than zero Scale is greater than 38 Scale is greater than Precision |
|
| DT_CY | The decimal data type is mapped to DT_CY when it is declared as a money data type. | |
| SQL_DATE SQL_TYPE_DATE |
DT_DBDATE | |
| SQL_TIME SQL_TYPE_TIME |
DT_DBTIME | |
| SQL_TIMESTAMP SQL_TYPE_TIMESTAMP |
DT_DBTIMESTAMP DT_DBTIMESTAMP2 |
SQL_TIMESTAMP data types are mapped to DT_DBTIMESTAMP2 if scale is greater than 3. In all other cases, they are mapped to DT_DBTIMESTAMP. |
| SQL_CHAR SQLVARCHAR |
DT_STR DT_WSTR DT_TEXT DT_NTEXT |
DT_STR is used if the column length is less than or equal to 8000 and the ExposeStringsAsUnicode property is false. DT_WSTR is used if the column length is less than or equal to 8000 and the ExposeStringsAsUnicode property is true. DT_TEXT is used if the column length is greater than 8000 and the ExposeStringsAsUnicode property is false. DT_NTEXT is used if the column length is greater than 8000 and the ExposeStringsAsUnicode property is true. |
| SQL_LONGVARCHAR | DT_TEXT DT_NTEXT |
DT_NTEXT is used if the ExposeStringsAsUnicode property is true. |
| SQL_WCHAR SQL_WVARCHAR |
DT_WSTR DT_NTEXT |
DT_WSTR is used if the column length is less than or equal to 4000. DT_NTEXT is used if the column length is greater than 4000. |
| SQL_WLONGVARCHAR | DT_NTEXT | |
| SQL_BINARY | DT_BYTE DT_IMAGE |
DT_BYTES is used if the column length is less than or equal to 8000. DT_IMAGE if the column length is greater than 8000. |
| SQL_LONGVARBINARY | DT_IMAGE | |
| SQL_GUID | DT_GUID | |
| SQL_INTERVAL_YEAR SQL_INTERVAL_MONTH SQL_INTERVAL_DAY SQL_INTERVAL_HOUR SQL_INTERVAL_MINUTE SQL_INTERVAL_SECOND SQL_INTERVAL_YEAR_TO_MONTH SQL_INTERVAL_DAY_TO_HOUR SQL_INTERVAL_DAY_TO_MINUTE SQL_INTERVAL_DAY_TO_SECOND SQL_INTERVAL_HOUR_TO_MINUTE SQL_INTERVAL_HOUR_TO_SECOND SQL_INTERVAL_MINUTE_TO_SECOND |
DT_WSTR | |
| Provider Specific Data Types | DT_BYTES DT_IMAGE |
DT_BYTES is used if the column length is less than or equal to 8000. DT_IMAGE is used if the column length is zero or greater than 8000. |
In This Section
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for