Teradata tools and utilities

SAS/ACCESS Interface to TeradataFor general information about this feature, seeOverview of the SQL Pass-Through Facility. Teradata examples areavailable.Here are the SQL pass-through facility specifics forthe Teradata interface.The dbms-name is TERADATA.TheCONNECT statement is required.The Teradata interface can connect to multipleTeradata servers and to multiple Teradata databases. However, if you use multiplesimultaneous connections, you must use an aliasargument to identify each connection.The CONNECT statement database-connection-arguments are identical to the LIBNAMEconnection options.The MODE=LIBNAME option is available with the CONNECT statement. By default, SAS/ACCESS opensTeradata connections in ANSI mode. In contrast, most Teradata tools, suchas BTEQ, run in Teradata mode. If you specify MODE=TERADATA, Pass-Throughconnections open in Teradata mode, forcing Teradata mode rules for all SQLrequests that are passed to the Teradata DBMS. For example, MODE= impactstransaction behavior and case sensitivity. See your Teradata SQL referencedocumentation for a complete discussion of ANSI versus Teradata mode.By default, SAS/ACCESS opensTeradata in ANSI mode. You must therefore use one of these techniques whenyou write PROC SQL steps that use the SQL pass-through facility.Specify an explicit COMMIT statement to closea transaction. You must also specify an explicit COMMIT statement after anyData Definition Language (DDL) statement. The examples below demonstrate theserules. For further information about ANSI mode and DDL statements, see yourTeradata SQL reference documentation.Specify MODE=TERADATA in your CONNECT statement.When MODE=TERADATA, you do not specify explicit COMMIT statements as describedabove. When MODE=TERADATA, data processing is not case sensitive. This optionis available when you use the LIBNAME statement and also with the SQL pass-throughfacility.CAUTION:Donot issue a Teradata DATABASE statement within the EXECUTE statement in PROCSQL. Add the SCHEMA= option to your CONNECT statement if you must change thedefault Teradata database. In this example, SAS/ACCESS connectsto the Teradata DBMS using the dbcon alias.proc sql; connect to teradata as dbcon (user=testuser pass=testpass); quit;In the next example, SAS/ACCESS connectsto the Teradata DBMS using

Etc.). See Set Connection Categories for more information. Click here to view a video of this feature. Options tabDescription Isolation Level(Optional) Select the default isolation level for this connection.Lifetime(Optional) Specify a maximum value for connection lifetime by entering the length of time in seconds. The default value is 0, which specifies no maximum.Click Connect to connect immediately while saving the connection information. Optionally, click Save to save the connection without connecting.Note: An SAP HANA ODBC driver is installed along with Toad Data Point and is required if using a HANA connection in a cross-connection query.Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory link in Help | About. Create Teradata ConnectionsToad allows you to create a full-featured, native Teradata connection. This type of connection provides complete Query Builder, Query Editor, and Database Explorer functionality.Notes: You must have a Teradata .NET Data Provider installed before you can create a Teradata connection. To use a Teradata connection in a cross-connection query, you must have a Teradata ODBC driver installed. To install the Teradata data provider» Download and install the Teradata .NET Data Provider from the Teradata Web site. Use the version supported by your database version.To create a Teradata connectionClick on the Connections toolbar (ALT+F+N). Select Teradata from the Group list. Enter the connection information in the Create New Connection dialog. Review the following for additional information:Login Server nameEnter the name of the server hosting the database to which you want to connect.Use Single Sign OnSelect if the user is using a single sign-on authentication system.PasswordEnter the password to use when connecting.Tip: After connecting, you can set a master password to further secure your connection in Tools | Options| Environment | Security. Session modeDisplays the session mode, which is always Teradata in a Toad Teradata connection.Default database(Optional) Enter a default Teradata database for the connection.Category(Optional) Select an existing category or create a new one. See Set Connection Categories for more information.Advanced Mechanism(Optional) Select an authentication mechanism from the list.Parameter(Optional) Enter the parameters required by the selected authentication mechanism. See your system administrator for

The options are shown in the following table:OptionDescriptionTable name - TPT ExportRetrieve data from a table or a view in the Teradata data source. When this option is selected, select an available table or view from the list for Name of the table or the view.SQL command - TPT ExportRetrieve data from the Teradata data source by using a SQL query. When this option is selected, enter a query in one of the following ways: Enter the text of the SQL query in the SQL command text field.Select Browse to load the SQL query from a text file.Select Parse query to verify the syntax of the query text.PreviewSelect Preview to view up to the first 200 rows of the data that's extracted from the table or view you selected.The Columns paneUse the Columns pane to map an output column to each external (source) column. To open the pane:In SQL Server Data Tools, open the SSIS package that contains the Teradata source.On the Data Flow tab, double-click the Teradata source.In Teradata Source Editor, select the Columns tab.OptionsAvailable External ColumnsThis table lists the available external columns that you can select to add to the External Column list. You can list the columns in the order you choose. You can't use this table to add or delete columns.To choose all columns, select the Select All check box.External ColumnsThe external (source) columns that you selected are listed in order. To change the order, first clear the Available External Column list, and then select the columns in a different order.Output ColumnAlthough the name of the selected external (source) column is the default output name, you can enter any unique name.NoteIf there are columns that contain unsupported data types, you'll receive a warning that displays those unsupported data types, and the relevant columns will be removed from the mapping columns.The Error Output paneUse the Error Output pane to select error-handling options. To open the pane:In SQL Server Data Tools, open the SSIS package that contains the Teradata source.On the Data Flow tab, double-click the Teradata source.In Teradata Source Editor, select the Error Output tab.OptionsError behaviorSelect how

Supports NoSpool. NoSpoolOnly: Do not use Spool in any case. The job will be terminated with an error if the DBS does not support NoSpool.SqlCommandStringThe SQL command to be executed when AccessMode is set to SQL Command.TableNameStringThe name of the table containing the data to be used when AccessMode is set to Table Name.TenacityHoursIntegerThe number of hours the TPT driver attempts to log in when the maximum number of load/export operations are already running. The default value is 4 hours. This property is in the Advanced Editor pane.TenacitySleepIntegerThe number of minutes the TPT driver pauses before attempting to log in when the limit is reached. The limit is defined by the MaxSessions and TenacityHours properties. Default value is 6 minutes. This property is in the Advanced Editor pane.UnicodePassThroughBooleanOff (default value): Disable Unicode pass-through. On: Enable Unicode pass-through.Configure the Teradata sourceYou can configure the Teradata source programmatically or by using SQL Server Integration Services (SSIS) Designer.The Teradata Source Editor pane is shown in the following image. For more information, go to each of the following Teradata Source Editor sections:The Connection Manager paneThe Columns paneThe Error Output paneThe Advanced Editor pane contains properties that can be set programmatically. To open the pane:On the Data Flow page of your Integration Services project, right-click the Oracle source, and then select Show Advanced Editor.For more information about the properties that you can set in the Advanced Editor pane, see Teradata source custom properties.The Connection Manager paneUse the Connection Manager pane to select the Teradata connection manager instance for the source. In this pane, you can also select a table or a view from the database. To open the pane:In SQL Server Data Tools, open the SSIS package that contains the Teradata source.On the Data Flow tab, double-click the Teradata source.In Teradata Source Editor, select the Connection Manager tab.OptionsConnection managerSelect an existing connection manager from the list, or select New to create a new Teradata connection manager instance.NewSelect New. The Teradata Connection Manager Editor pane opens. From this pane, you can create a new connection manager.Data Access ModeChoose a method for selecting data from the source.

That the table name in the generated script here will be the name of the OLE DB Destination task name.Click OK would create the WIDE_TABLE table in the destination SQL Server database as configured in the Destination Connection manager.After clicking the OK button and the table is created, the form will go back to the OLE DB Destination Editor. There is a Warning sign at the bottom of the form to map the columns on the Mapping page.Step 7 – Correct Column Mappings in Integration ServicesClick on the Mappings page and this will automatically map the source to destination columns based on the same column name created.Now the Data Flow Task is complete and all the previous errors and warnings are gone.Step 8 – Execute Integration Services PackageRight-click on the SSIS package and choose “Execute Package” to kick off the package execution.Step 9 – Verify the Data Export from Teradata to SQL ServerIn SSMS, you can verify that the new WIDE_TABLE table has been created and contains all the columns of the source Teradata table.Next StepsTry this out next time you need to import data into SQL Server.Also, check out these other articles: SQL Server Data Tools Task 4: Creating an SSIS Project using SQL Server Data Tools Installing Teradata Client Tools to use with SQL Server Integration Services Integration Services (SSIS) Connections Check out all of the Integration Services tips