By: Junaith Haja | 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.
Select the features you want to install. I would suggest selecting all features to avoid any later installation process and then click Install.
The installation process begins.
It will give a confirmation when the installation is complete.
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.
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.
Enter the credentials provided by your Teradata 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.
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.
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.
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.
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.
About the author
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
