Teradata Destination

SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

All Downloads ----- Questions?

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 destination component dialog. 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


Demonstration

Setup

Use the parameters below to set up the component.

Parameters

IDbConnection

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

OperatorType

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

Specify the destination database table where the data is to be loaded.

ErrorLimit

Specify the maximum number of errors allowed before the component stops processing. The default is 0.

DataEncryption

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

Specifies an error table. If not specified, a default error table will be set up with a name based on the selected destination table.

MinSessions

Specifies the minimum number of sessions to be used during processing. The default is 1.

MaxSessions

Specifies the maximum number of sessions to be used during processing. The default is 4.

TraceFile

Specifies the name of the external file to use for tracing messages.

TenacityHours

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.

TenacitySleep

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.

AppendErrorTable

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

How to modify 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 [Teradata 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.

Case Study

Case study by Cathay Bank.

Samples

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