This site uses cookies to deliver our services. By using this site, you acknowledge that you have read and understand our Cookie and Privacy policy. Your use of Kontext website is subject to this policy. Allow Cookies and Dismiss

Create ETL Project with Teradata through SSIS

8531 views 2 comments last modified about 4 years ago Raymond

Infosphere DataStage is adopted as ETL (Extract, Transform, Load) tool in many Teradata based data warehousing projects. With the Teradata ODBC and .NET data providers, you can also use the BI tools from Microsoft, i.e. SSIS.

In my previous post, I demonstrated how to install Teradata Toolkit & Utilities in Windows. Once we installed the ODBC driver and Teradata .NET data provider, you can use Teradata as data source or target in any SSIS package. In this post, I will create a SSIS project to load data from Teradata into a CSV file. It will be the same to load data into Teradata from other sources since one data source can also act as target/destination.

Prerequisites

Teradata ODBC Driver or Teradata .NET Data Provider

If you have not installed any of them, please install following the link below:

Connect to Teradata Virtual Machine Guest from Windows Host

SSIS (SQL Server Integration Service)

You can install Data Tools for Visual Studio to create ETL projects. I am using Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2013 (https://www.microsoft.com/en-au/download/details.aspx?id=42313).

Create the ETL Project

Open Visual Studio and create a SSIS project named ‘SSIS_TeraData_Test’.

The project will looks like the following screenshot:
image

Create Connection Manager to Teradata

Right-click the folder Connection Managers; in the context menu, click ‘New Connection Manager’ to open the wizard.

You can select ODBC type but in my sample, I will choose ADO.NET as the connection manager type.

image

Click ‘Add..’'.’ button.

image

Click the ‘New…’ button to add a new connection manager.

image

Select ‘.NET Data Provider for Teradata’ as the Provider.

Specify the following connection properties:

  • Server name: 192.168.121.168 (Teradata instance address)
  • User: dbc
  • Password: dbc

image

Click the Test Connection button to ensure it can be successfully connected.

image

In the All tab, you can specify more connection properties. I will just leave them as default.

Click the ‘OK’ button to finish. You can find the connection manager in Connection Managers folder. It can be used by all the packages in the project.

image

Modify the Package

Open the package ‘Package.dtsx’ under SSIS Packages folder.

Drag the Data Flow Task from SSIS Toolbox window into the package design area.

image

Double click the data flow task and you can then design the steps in the flow.

In the Data Flow design area, drag the ADO NET Source into the workspace.

image

In the ADO Source Editor dialog, specify these properties:

  • Connect manager: the one created previously
  • Data access mode: SQL command
  • SQL command test:
SELECT    Emp.EmployeeID,
    Emp.EmployeeName,
    Dept.DepartmentName
    FROM TD_MS_SAMPLE_DB.Employee Emp
    INNER JOIN TD_MS_SAMPLE_DB.Department Dept
    ON Dept.DepartmentID = Emp.DepartmentID
    ORDER BY Emp.EmployeeID;

image

You can click Preview button to preview the data.

image

Back to the Editor, you can change the column names in Output Column in the Columns tab.

image

Click ‘OK’ to finish.

Drag Flat File Destination from the Other Destinations into the working area and connect it to ADO NET Source’s output link.

image

Double click the Flat File Destination to edit the properties.

In the Flat File Destination Editor, click New button to create another connection manager.

Select the Flat File Format as Delimited and then click OK to continue.

image

In the Flat File Connection Manager Editor, input the values as shown in the screenshot.

image

image

Click OK button to save this connection manager.

Back to the Flat File Destination Editor, click Mappings tab to map the columns.

image

Click OK button to save these configurations.

Until now, we have created the package to load employee data into a CSV file.

Run the Package

Right-click the package name in the Solution Explorer, and click Run Package to run the process.

image

From the above screenshot, we can know this data flow task was executed successfully and there were 9 rows inserted into the target file.

Navigate to C drive, you can find the created file TestTeradata.csv. Open the file, you can find all the employees were extracted to the file.

image

SSIS Data Flow Tasks

With SSIS Data Flow Task, you can do many transformations as you would do in any other ETL tools.

The followings are the available functions and all the data source/target types it supports.

imageimageimage

I am not covering SSIS details in this post as it requires hundreds of pages to illustrate. If you are interested, you can always visit Microsoft official site for all the details you need:

https://technet.microsoft.com/en-US/library/bb522537.aspx

Related pages

Useful DBC (Data Base Computer) System Views in Teradata

204 views   0 comments last modified about 4 months ago

This page summarize some of the commonly used views in Teradata. Conventions In all the views in the following sections, X views are also available though they only return rows that contain information on objects that the requesting database user owns, created, granted privilige on,...

View detail

Install Teradata Express 15.0.0.8 by Using VMware Player 6.0 in Windows

12442 views   23 comments last modified about 5 years ago

In this article, I am going to introduce how to install Teradata Express in virtual machines in Windows. Download software 1) Download VMware Player for Windows 32-bit and 64-bit from the following link (version 6.0): ...

View detail

Use Hadoop File System Task in SSIS to Write File into HDFS

460 views   0 comments last modified about 6 months ago

Context SQL Server Integration Service ( SSIS ) has tasks to perform operations against Hadoop, for example: Hadoop File System Task Hadoop Hive Task Hadoop Pig Task In Data Flow Task, you can also use: Hadoop HDFS Source ...

View detail

Connect to Teradata database through Python

1687 views   0 comments last modified about 8 months ago

Teradata published an official Python module which can be used in DevOps projects. More details can be found at the following GitHub site: https://github.com/Teradata/PyTd Install Teradata module ...

View detail

Teradata Tutorials Summary - October 2017

241 views   0 comments last modified about 11 months ago

This post summarizes the tutorials I created in the past about Teradata. It is trying to help you gain the basic SQL skills with Teradata especially if you are a new beginner or you just move from SQL Server or Oracle. It also includes some guides about setting up your own Teradata server (latest Teradata Express version TDExpress16.10.00.03_Sles11_40GB.7z).

View detail

Setup Teradata in Microsoft Azure

220 views   0 comments last modified about 11 months ago

This page provides the steps to create a Teradata database in Microsoft cloud computing platform Azure.

View detail

Add comment

Please login first to add comments.  Log in New user?  Register

Comments (2)

R Re: Create ETL Project with Teradata through SSIS

Ra*** about 11 months ago

@Sasi Kumar

Apologies  for the late reply.

You can pass variables as you can do with any database, I.e. use variable expressions to prepare the statement.

Sas*** about 2 years ago

How to pass user variables in the query.
SK Re:Create ETL Project with Teradata through SSIS

Sas*** about 2 years ago

How to pass user variables in the query.