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.


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 (

Create the ETL Project

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

The project will looks like the following screenshot:

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.


Click ‘Add..’'.’ button.


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


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

Specify the following connection properties:

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


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


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.


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.


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.


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,
    FROM TD_MS_SAMPLE_DB.Employee Emp
    INNER JOIN TD_MS_SAMPLE_DB.Department Dept
    ON Dept.DepartmentID = Emp.DepartmentID
    ORDER BY Emp.EmployeeID;


You can click Preview button to preview the data.


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


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.


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.


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



Click OK button to save this connection manager.

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


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.


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.


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.


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:

info Last modified by Raymond at 8 months ago * This page is subject to Site terms.

More from Kontext

local_offer teradata local_offer python

visibility 560
thumb_up 1
access_time 3 months ago

Pandas is commonly used by Python users to perform data operations. In many scenarios, the results need to be saved to a storage like Teradata. This article shows you how to do that easily using JayDeBeApi or  ...

open_in_new Spark + PySpark

local_offer teradata local_offer SQL

visibility 52
thumb_up 0
access_time 3 months ago

In SQL Server, we can use TRUNCATE statement to clear all the records in a table and it usually performs better compared with DELETE statements as no transaction log for each individual row deletion. The syntax looks like the following: TRUNCATE TABLE {

open_in_new Code snippets

local_offer teradata local_offer python local_offer Java

visibility 309
thumb_up 0
access_time 3 months ago

Python JayDeBeApi module allows you to connect from Python to Teradata databases using Java JDBC drivers. In article Connect to Teradata database through Python , I showed ho...

open_in_new Python Programming

local_offer teradata local_offer SQL

visibility 222
thumb_up 0
access_time 3 months ago

OREPLACE functions in Teradata can be used to replace or remove characters from a string. OREPACE is Teradata's extension to ASNI SQL. The usual REPLACE function is not available. ANSI SQL REPLACE function REPLACE function is commonly implemented in many other SQL databases such as ...

open_in_new Code snippets

info About author

comment Comments (4)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts


I don't have SSIS in my current PC any more. However it should work.

Are there are any objects in your database? And also does your account have access to list the tables or views in the database?


person Arul access_time 2 years ago
Re: Create ETL Project with Teradata through SSIS

after the configuration step instead of using SQL command have you tried tables or views in dropdown ? cause i have tried and i'm getting no tables or views could be loaded. 
reply Reply
account_circle Arul
after the configuration step instead of using SQL command have you tried tables or views in dropdown ? cause i have tried and i'm getting no tables or views could be loaded. 
reply Reply
account_circle Raymond

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


person Sasi Kumar access_time 3 years ago
Re:Create ETL Project with Teradata through SSIS

How to pass user variables in the query.
reply Reply
account_circle Sasi Kumar
How to pass user variables in the query.
reply Reply

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.

Learn more arrow_forward