SSIS and Global Temp Tables

Database

SSIS and Global Temp Tables

Hi. Can someone please tell me how Global Temp Tables work in Teradata?  Apparently, a connection to Teradata creates an instance of the table, and any data inserted into it is associated with that instance.  I am using Data Flow Tasks in SSIS 2008 to insert data into a temp table.  When I reference the temp table in a separate Data Flow Task, I get another instance of the table, which is empty.  I'm using 'On Commit Preserve Rows'.  

With Teradata SQL Assistant, I can insert data into the temp table, and retrieve it.  But, if I drop the connection and reconnect, the data is gone. Can someone please tell me how to insert data into the temp table, and use it before the data is dropped?  Thanks!

4 REPLIES
Teradata Employee

Re: SSIS and Global Temp Tables

In your case, temporary tables .... be it Global TT or Volatile TT .... wont work.

Because, for GTT it retains its definition across all sessions, but data is only visible to session that inserted it.

VTTs on the other hand, gets dropped as soon as session is disconnected which created them.

You should use permanent table for your task for the data to be available across multiple sessions.

HTH!

Re: SSIS and Global Temp Tables

Richard,

Global temporary table base definition is stored in data dictionary. When you create a GTT, an instance of this base definition is created against the session. Temporary table instance will be dropped at the session termination. So when you disconnect the session, temp table is dropped. 

If there is a compulsion to disconnect the session, then it would be difficult to use either Volatile or temp table. you can create a normal table and use it, and then drop it manually at the transaction end.

Khurram

Re: SSIS and Global Temp Tables

Thanks Adeel for your advice, and thanks Saeed for your help too.  I was able to create a permanent table in EDW, and that solved it. You guys really rock! 

Teradata Employee

Re: SSIS and Global Temp Tables

Welcome Richard! :)