Installing Teradata Client Tools to use with SQL Server Integration Services

By:   |   Updated: 2014-10-17   |   Comments (6)   |   Related: More > Integration Services Development


Problem

The company where I work migrated their data warehousing environment to Teradata. The requirement is to download and install the Teradata Client utilities to access the newly built data warehouse and to extract data from Teradata to SQL Server using SQL Server Integration Services.

Solution

Teradata is one of top notch data warehouse DBMS products available in the market today and it's built by consolidating data from different sources like any other data warehouse. The data stored can be used to drive analytics like tracking sales of an organization and measuring performance of a product or customer experience.

To access data stored in Teradata we need to install the Teradata Tools and Utilities (their client side product) and the .NET Data Provider for Teradata (an extension built on Microsoft's ADO.NET platform).  This enables us to connect to a Teradata database and to load or retrieve data using SSIS in BIDS/Visual Studio.

Installation of Teradata Client Side Tools

Go to the Teradata Tools and Utilities page and download the TTU 15.0 Installer File.

This is a zip file which you will need to extract. Run the TTU_Base.exe file from the extracted folder.

The Installation Wizard will pop-up.  Select the language as English and click Next.

By default Teradata will want to install in your C:\Program Files\Teradata\Client\15.0 folder. If you want to change the location, change it at this step.

Installation of Teradata Client Side Tools

Select the features you want to install. I would suggest selecting all features to avoid any later installation process and then click Install.

Select the features you want to install

The installation process begins.

The Installation process begins.

It will give a confirmation when the installation is complete.

It would give a confirmation once the installation is completed.

You can see the list of Teradata products installed in your machine from the Windows > All Programs menu as shown below. These will match the features selected in the installation process.

You can see the list of Teradata products installed in your machine from the Windows-> All Programs Menu

Open the SQL Assistant which is similar to Microsoft's SSMS from Windows > All Programs > Teradata SQL Assistant or from C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Teradata Client 15.00\Teradata SQL Assistant. This is Teradata's equivalent of Microsoft SQL Server Management Studio.

Change the Data Provider to Teradata.Net and click the Connect button found in the top left corner (as shown below) of the screen to establish a connection.

Change the Data Provider to Teradata.Net

Enter the credentials provided by your Teradata DBA in the Connection Information screen.

Enter the credentials provided by your DBA in the Connection Information Screen.

Check the Use Integrated Security box if your Teradata username and password is same as your Windows network login and password.

If you are connecting by LDAP/SPNEGO/TD2, select the appropriate option from the Mechanism dropdown and click OK.

Now you will be able to connect to the Teradata database and can query any tables.

Connecting to Teradata from SQL Server Integration Services

Note we have also installed the .NET Data Provider for Teradata as part of our client side tools which we will use to connect to Teradata from Visual Studio/BIDS.

Now open an Integration Services Project in Visual Studio. I have used Visual Studio 2013 for this demo. Right click the Connection Manager and click New ADO.NET Connection and open a new connection.

Now open an Integration Services Project in Visual Studio

In the Configure ADO.NET Connection Manager Window, enter the Teradata server credentials used to connect to Teradata through SQLAssistant. Test the connection and click OK.

Enter the Teradata Server credentials used to connect to Teradata through SQLAssistant.

Drag a data flow task to the Designer and add an ADO.NET Source and OLEDB/SQL Server Destination task. Connect the ADO.NET Source to the new Teradata Connection Manager created and the Destination to the SQL Server Connection Manager and select the appropriate source and destination tables.

Run the Data Flow Task, the source table from Teradata will be extracted to SQL Server destination.

Run the Data Flow Task, the source table from Teradata will be extracted to SQL Server destination.

We are done with our solution to extract a Teradata table to our SQL Server database.

Query Timeout Issue in SSIS

You may run into a Query Time out Error for the ADO.NET Source task when it tries to connect to the Teradata server. Right click the ADO.Net Source task and open its Properties. By default your command timeout will be 30 seconds. Set it to 100 seconds or more to eliminate a connection timeout issue.

You may run into a Query Time out Error at ADO.NET Source task when it tries to connect to the Teradata Server
Next Steps
  • The same process could be done using the ODBC Connection for which you will add an ODBC source with Teradata credentials.  The necessary ODBC driver needed to connect with Teradata has already been installed during the above installation process. Try it out and let us know which performs faster. I bet the .NET data provider using Teradata will be faster than ODBC as it's specially designed to work with Teradata.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Junaith Haja Junaith Haja is a Senior Business Intelligence Consultant with Browse Info Solutions, Inc and leads a Microsoft SQL Server and BI team.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2014-10-17

Comments For This Article




Wednesday, May 27, 2020 - 4:17:03 PM - Gaurav Back To Top (85786)

Hi , I am unable to see tab of connection in Connection manaer while creating with  (.net data provider for Teradata).

second option All available to connect with. please suggest.

Friday, April 24, 2015 - 8:52:28 PM - David S Back To Top (37031)

If you have to pump a lot of data to/from SQL Server and Teradata, you need to utilize the free Attunity Teradata Connectors (download from Microsoft).  They use the Teradata Parallel Transport API which is 100's of times faster.  There are some weird limitations to it, but it does the job faster than pretty much anything else you can do for free.

Monday, October 20, 2014 - 10:29:34 AM - Junaith Haja Back To Top (35020)

@Scott: Thank you Scott for sharing your experience. Even I started using ODBC for this requirement and had terrible experience when I moved the packages to production and then found this approach which had a better performance.

 

@Safi: Thank you.!

Friday, October 17, 2014 - 11:38:05 PM - Syed Safiullah Ahmed Back To Top (35003)

Thank you Junaith, this is very informative. 

 

Safi

Friday, October 17, 2014 - 11:14:22 AM - Scott Back To Top (34993)

I hope you get decent performance from the .Net provider, but I would test it to get a comparison.

I worked with Teradata imports/exports about ten years ago, and the OLE DB provider performance was terrible.  To add insult to injury, we had an Access guy who bragged all the time about how much faster his code was (using ODBC) than our SQL Server code.

We ended up almost exclusively using FastLoad/FastExport.  They are no fun to set up, but it was the only way to get decent performance with large data sets.

Friday, October 17, 2014 - 9:49:03 AM - Jeremy Kadlec Back To Top (34991)

Junaith,

Congrats on your first tip!

Thank you,
Jeremy Kadlec
Community Co-Leader














get free sql tips
agree to terms