How do you write to Teradata in SSIS?

Question

How do you write to Teradata in SSIS?

mbmalm

SSC-Addicted

Points: 487
More actions
November 28, 2011 at 2:28 pm

#263549

I read from Teradata for many SSIS projects. Now I have the need to write to it. I'm reading from DB2 and writing to Teradata. I assumed I could do it in a Data Flow. My connection to Teradata is a .Net connection, can't see a way to reference Teradata as a Data Flow Destination.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

You need to create a datasource to teradata in the lower section and then use the corresponding destination component, most likely OLDB.

From the first couple of hits in google (Keywords: ssis teradata data source):

http://www.google.com/url?sa=t&rct=j&q=&esrc=s&frm=1&source=web&cd=5&ved=0CEoQFjAE&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2FD%2FB%2FD%2FDBDE7972-1EB9-470A-BA18-58849DB3EB3B%2FSSIS_Teradata_Connectors.docx&ei=gQfUTv7fBYT30gH04vkl&usg=AFQjCNEwaEB9pj48EbZCXXZ26N-4IDndpA&sig2=xvYh3HQhaX9NTA4syuKwaw

http://forums.teradata.com/forum/third-party-software/ms-ssis-oledb-command

Which references this: http://developer.teradata.com/blog/netfx/2009/06/ little-known-secret-about-microsoft-bi-tools-and- teradata

and a microsoft download:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11180

Google is your friend. Love the google.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Evil Kraig F (11/28/2011)
Google is your friend. Love the google.

Hmmm, I should put this in my signature. 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mbmalm SSC-Addicted Points: 487 More actions · Answer 3

I was confused by the answer. I guess your using vs2008 and I'm using vs2005. Many of our servers are 2005. From my understanding I can't create in vs2008 and expect it to run on a 2005 server. I see the additional options available in vs2008. It would make my life a bit easier. We use ADO.Net connection to Teradata but that's not option in 2005 Data Flow Destinations. I'll probably have to try to get the DBA's to allow us to create batch jobs on our test server which is 2008.

mbmalm SSC-Addicted Points: 487 More actions · Answer 4

I have created the SSIS package I needed using VS2008. It works using ADO.NET as destination.

deloiter SSC Rookie Points: 45 More actions · Answer 5

I also needed to write to Teradata using 2005 SSIS and as others have discovered there is no available destination object using the standard installation. I was not able to install the Attunity drivers or move to SSIS 2008, so I created a work-around. It is not particularly efficient or elegant, but it works very well for small datasets. I never need to push more than about 10,000 rows to Teradata and this works OK for that.

My SSIS package

I first created variables:

Object data type variable (gblt_list, for instance), the values to pass are stored here

String data type variable to hold a loop value (gbl_loopvalue, for instance)

String data type variable to hold a SQL insert statement (gbl_SQL_insert2T, for instance). Set the Expression in the properties to: "insert into YourTeradataTableName (YourColumnName) values ('"+@[User::gbl_loopvalue]+"');"

Then I inserted the values into that Object variable using an Execute SQL Task Editor

General tab

ResultSet: Full result set

ConnectionType: OLE DB (in my case)

Connection: MySQLServer\Instance.DatabaseName

SQLStatement: your SQL select statement (mine is: select AccountNumber from dbo.Accounts where SalesID = 13)

Result Set tab

Result Name: 0

Variable Name: User::gblt_list

Next using the Foreach Loop Editor

Collection tab

Enumerator: Foreach ADO Enumerator

ADO object source variable: User::gblt_list

Enumeration mode: Rows in the first table

Variable Mappings tab

Variable: User::gbl_loopvalue

Index: 0

Inside the Foreach Loop container create a SQL task

General tab

ResultSet: None

ConnectionType: whatever you use to connect to Teradata (ADO.NET in my case)

SQLSourceType: Variable

SourceVariable: User::gbl_SQL_insert2T

Connect each of the above object in order, save and debug. Good luck.

arun7787 Old Hand Points: 300 More actions · Answer 6

arun7787

Old Hand

Points: 300

January 18, 2012 at 2:30 am

#1435059

I tried it....

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

arun7787 (1/18/2012)
I tried it....

And...?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

COZYROC One Orange Chip Points: 28499 More actions · Answer 8

CozyRoc have just released SSIS+ 1.6 , which includes Teradata Destination component. It is supported on all versions: 2005, 2008, 2008R2, 2012

You can check the documentation for the new component here.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

To be utterly complete:

you can download SSIS connectors for Teradata by Attunity for free here:

Microsoft Connectors Version 1.1 for Oracle and Teradata by Attunity

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP