All Forums Database
Richard34 9 posts Joined 02/14
05 Feb 2014
Export from SQL Server to Teradata Temp tables

Hello.  I am trying to export data from SQL server to a temporary table in Teradata.  The temp table will then be joined to a view in Teradata, and the results exported back to SQL server.  I've tried doing this in SSIS, but have not been successful.  Any help would be greatly appreaciated!  Thanks.

Adeel Chaudhry 773 posts Joined 04/08
05 Feb 2014

Any specific need of using temp table? And is it global temporary or volatile table?

-- If you are stuck at something .... consider it an opportunity to think anew.

Adeel Chaudhry 773 posts Joined 04/08
05 Feb 2014

Also, any specific error you are facing?

-- If you are stuck at something .... consider it an opportunity to think anew.

M.Saeed Khurram 544 posts Joined 09/12
06 Feb 2014

Richard,
 
Whats the issue you are facing with SSIS? 
 
 

Khurram

M.Saeed Khurram 544 posts Joined 09/12
06 Feb 2014

Can you please also tell how much data do you have to process?
 

Khurram

Richard34 9 posts Joined 02/14
06 Feb 2014

Thanks Adeel and Saeed for your responses.  I don't have permission to create a permanent table on the server. (What is the difference between a global table and a permanent table?) A global table would be great if it persists on the server.  I tried creating a temp table through an OLE DB connection to Teradata, but it didn't recognize keyword Volatile.  I am using an OLE DB dataflow source in SSIS to retrieve data from SQL Server for the temp table.  This pulls 170 records. I then need to join this new temp table with a view to filter data in Teradata.  The view returns about 50K records.  The result set needs to be exported back to SQL server.  Please advise how to create and populate a temp table in Teradata with data from SQL Server, join to a view, and export the results back to SQL Server.

Richard34 9 posts Joined 02/14
06 Feb 2014

Thanks again!

Adeel Chaudhry 773 posts Joined 04/08
07 Feb 2014

You could use Global Temporary Table, and yes they persist on the server.
 
Other way around is to just pull the data to MS SQL Server and make this join there.

-- If you are stuck at something .... consider it an opportunity to think anew.

Richard34 9 posts Joined 02/14
07 Feb 2014

Thanks Adeel!  I'm looking at creating a global table through an ADO connection to Teradata.  If I'm not able to create the table, I'll pull it to SQL Server as you suggested.

Adeel Chaudhry 773 posts Joined 04/08
08 Feb 2014

Welcome Richard! :)

-- If you are stuck at something .... consider it an opportunity to think anew.

Richard34 9 posts Joined 02/14
10 Feb 2014

Thanks for the welcome! I'm glad I found this site.

 

- I'm able to perform the join in SQL Server this time, however, any pointers on populating a temp table through SSIS would be helpful for future tasks.  I'm able to create a global table through Teradata Assistant, but I'm not able to find it within SSIS when connecting to Teradata through OLE DB or ADO.  Thanks everyone.

vasu246 1 post Joined 02/14
10 Feb 2014

what is a global temporary table in teradata?

M.Saeed Khurram 544 posts Joined 09/12
11 Feb 2014

Vasu,
A global Temporary table is a temporary table that uses temporary space for the storage. A base definition of the table is stored in the data dictionary, an instance of this table is created with each create temporary table definition. Temporary tables survives a system restart, so more suitable for automatic scripts to avoid failures. Other tables kinds volatiles and derived tables do not survive a restart.
You can read more about this here:
http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1184_111A/Create_Table-Details.012.012.html
 

Khurram

abid17 1 post Joined 04/14
10 Apr 2014

I have the exact problem as Richard, but my data is too large to bring it down to SQL Server.  Any thoughts on how can I create a TD volatile table from SSIS ?
Thanks.

You must sign in to leave a comment.