COZYROC Teradata components, part of COZYROC SSIS+ suite since 2017, are third-party plug-ins for Microsoft SSIS that make it easy to integrate and migrate to and from Teradata. You can use any application, service or database supported by SQL Server Integration Services (SSIS) or COZYROC’s toolkit as the source or destination for Teradata. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components.
The Teradata integration package consists of two parts:
- Input type can be either a table or SQL statement.
- The source can be dynamically modified at runtime using an SSIS expression.
- Configurable parameters for block size, minimum and maximum sessions.
- Configurable parameters for operator type, error table, minimum/maximum sessions, tracing, etc.
- 80x faster loading speed compared to ODBC.
Overview
The Teradata Source Component is an SSIS Data Flow Component which connects to a Teradata database and bulk reads data from Teradata database tables. The configuration parameters are provided in the standard Data Flow Component dialogs. The component requires installation of Teradata Tools and Utilities 13.10 and above. Install the following Teradata components:
- Shared ICU libraries for Teradata
- Teradata GSS client
- ODBC Driver for Teradata
- CLIv2
- Teradata Parallel Transporter (Teradata PT)
- Teradata Parallel Transporter API
- Teradata PT Load Operator
- Teradata PT Stream Operator
- Teradata PT Update Operator
Teradata Tools and Utilities 13.10 post-installation steps
- Define TPT and TPT64 environment variables, referencing the root Teradata Parallel Transporter folder. The default folder is: \Program Files\Teradata\Client\13.10\Teradata Parallel Transporter
- Make sure the following folders are included in your PATH:
- \Program Files\Teradata\Client\13.10\ODBC Driver for Teradata\Lib\
- \Program Files\Teradata\Client\13.10\Shared ICU Libraries for Teradata\lib\
- \Program Files\Teradata\Client\13.10\Teradata Parallel Transporter\bin
- \Program Files\Teradata\Client\13.10\CLIv2
- \Program Files\Teradata\Client\13.10\Teradata Parallel Transporter\msg
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing ODBC connection manager. The ODBC connection manager is only used for connecting with the Teradata Parallel Transporter. The ODBC API is not used for the bulk-load process.
Related Topics: ODBC Connection Manager
Specify the input type. This parameter has the options listed in the following table.
Value Description Table Input type is Teradata table. Selecting this value displays the dynamic parameters Table. Statement Input type is a SQL statement. Selecting this value displays the dynamic parameter Statement. After making this change, use the Refresh command in the 'Column Mappings' tab to reload the source metadata.Select the source Teradata table.
Specify the SQL statement.
Specifies the block size in bytes used when returning data to the client. The minimum is 256 bytes. The default and maximum are 64330 bytes for Teradata Database V2R6.0 and later.
Indicates whether full security encryption of SQL requests, responses and data is to be used. This parameter has the options listed in the following table.
Value Description True All SQL requests, responses, and data are encrypted. False No encryption occurs. This is the default setting. Specifies the minimum number of sessions to be used during processing. The default is 1.
Specifies the maximum number of sessions to be used during processing. The default is 4.
Specifies the name of the external file to use for tracing messages.
Specifies the number of hours the Export driver attempts to log on when the maximum number of load and export operations are already running on the Teradata Database. The default value is four (4) hours.
Specifies the number of minutes the Export driver pauses before attempting to log on when the maximum number of load and export operations are already running on the Teradata Database. The default is six (6) minutes.
Knowledge Base
- Where can I find the documentation for the Teradata Source?
- How to modify the source dynamically at runtime
- After modifying the default Data Types in an ETL source, my modifications are lost the next time I open the Source component
What's New
- New: Introduced component.
Overview
The Teradata Destination Component is an SSIS Data Flow Component which connects to a Teradata database and bulk loads data into Teradata database tables. It is up to 80x faster than the standard loading using ODBC Destination. The configuration parameters are provided in the standard Data Flow Component dialogs. The component requires installation of Teradata Tools and Utilities 13.10. Install the following Teradata components:
- Shared ICU libraries for Teradata
- Teradata GSS client
- ODBC Driver for Teradata
- CLIv2
- Teradata Parallel Transporter (Teradata PT)
- Teradata Parallel Transporter API
- Teradata PT Load Operator
- Teradata PT Stream Operator
- Teradata PT Update Operator
Teradata Tools and Utilities post-installation steps
- Define TPT and TPT64 environment variables, referencing the root Teradata Parallel Transporter folder. The default folder is: \Program Files\Teradata\Client\13.10\Teradata Parallel Transporter
- Make sure the following folders are included in your PATH:
- \Program Files\Teradata\Client\13.10\ODBC Driver for Teradata\Lib\
- \Program Files\Teradata\Client\13.10\Shared ICU Libraries for Teradata\lib\
- \Program Files\Teradata\Client\13.10\Teradata Parallel Transporter\bin
- \Program Files\Teradata\Client\13.10\CLIv2
- \Program Files\Teradata\Client\13.10\Teradata Parallel Transporter\msg
Sample package
To test the performance of Teradata Destination against the standard ODBC Destination, see this package. In order to use this package, create a TEST_TABLE table with the following columns:
- COL1 INTEGER
- COL2 TIMESTAMP (6)
- COL3 FLOAT
- COL4 VARCHAR (200)
- COL5 VARCHAR (200)
- COL6 DECIMAL (9,5)
- COL7 VARCHAR (16000)
The NumberOfRows variable controls the number of inserted rows.
Demonstration
Case Study
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing ODBC connection manager. The ODBC connection manager is only used for connecting with the Teradata Parallel Transporter. The ODBC API is not used for the bulk-load process.
Related Topics: ODBC Connection Manager
Select the bulk-load driver. This parameter has the options listed in the following table.
Action Description Load Use the Load driver. Use this option when the destination table is empty and there are no secondary indexes defined. Update Use the Update driver. Use this option when the destination table already contains data. The destination is locked during processing. Stream Use the Stream driver. Use this option when you want to avoid locking of the destination table during processing. Specify the destination database table where the data is to be loaded.
Specify the maximum number of errors allowed before the component stops processing. The default is 0.
Indicates whether full security encryption of SQL requests, responses and data is to be used. This parameter has the options listed in the following table.
Value Description True All SQL requests, responses, and data are encrypted. False No encryption occurs. This is the default setting. Specifies an error table. If not specified, a default error table will be set up with a name based on the selected destination table.
Specifies the minimum number of sessions to be used during processing. The default is 1.
Specifies the maximum number of sessions to be used during processing. The default is 4.
Specifies the name of the external file to use for tracing messages.
Specifies the number of hours that the driver continues trying to log on when the maximum number of load and export operations are already running on the Teradata Database. The default value is four hours.
Specifies the number of minutes that the driver pauses before retrying to log on when the maximum number of load and export operations are already running on the Teradata Database. The default is six minutes.
Indicates whether or not the Stream Operator will use the existing error table.
Value Description True Stream operator will use the existing error table or create the
error table if it does not exist.False Stream operator will not use the existing error table (default).
Knowledge Base
- Where can I find the documentation for the Teradata Destination?
- Teradata Destination: How to modify the Teradata Destination table dynamically at runtime
What's New
- Fixed: Component failed when using column of type ByteInt (Thank you, Alan).
- New: Introduced component.
Teradata Destination
- Fixed: Component failed when using column of type ByteInt (Thank you, Alan).
- New: Introduced component.
Knowledge Base
- Where can I find the documentation for the Teradata Source?
- Where can I find the documentation for the Teradata Destination?
- How to modify the source dynamically at runtime
- Teradata Destination: How to modify the Teradata Destination table dynamically at runtime
- After modifying the default Data Types in an ETL source, my modifications are lost the next time I open the Source 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.