Export (0) Print
Expand All

ODBC Flow Components

This topic describes the concepts necessary for creating an ODBC data flow using SQL Server 2012 Integration Services (SSIS)

The Connector for Open Database Connectivity (ODBC) by Attunity for SQL Server 2012 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 2012 Integration Services (SSIS).

The ODBC source and ODBC destination for SQL Server 2012 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.

Before you can set up packages that use SQL Server 2012 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 2012 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)

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 2012 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 2012 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.

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)

DT_R8

DT_CY

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.

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

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)

DT_R8

DT_CY

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.

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

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.

Community Additions

ADD
Show:
© 2014 Microsoft