How to use temp tables as DTS destination tables in transform data tasks

Question

How to use temp tables as DTS destination tables in transform data tasks

nikhil8960

Right there with Babe

Points: 746
More actions
April 25, 2008 at 1:39 am

#71489

Hi all,
I am pretty new to sql server and still learning lot of new things. Recently I am facing an strange issue. In DTS transform data tasks I have seen persons using temp tables as destination. When I am trying to use the global temp tables in DTS destination, I am not able to see temp table present in database in destination tables under drop down box. Could anyone letme know how to make the temp table appear in drop down? Also is it possible to use temp tables as source in DTS transform tasks??......

Viewing 8 posts - 1 through 7 (of 7 total)

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

You can use Global Temporary tables as a transformation destination. There is an advisory article on msdn about the considerations of using temporary tables in DTS:

http://msdn2.microsoft.com/en-us/library/aa215269(SQL.80).aspx

HTH

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 2

Instead of using # temp tables or ## temp tables, why not create a table in tempdb, insert the data, process from it and then drop the table at the end of the DTS package?

Jez

nikhil8960 Right there with Babe Points: 746 More actions · Answer 3

Well you said it correect for using tempdb database but I am not having access to create table even in tempdb Database (DBA access issue 🙁 )so only I was thinking of using # temp table. Also I have seen some Approaches to populate temp tables which work very efficiently for my work so only wanted to use that. Could you tell me how to use temp table as DTS destination

nikhil8960 Right there with Babe Points: 746 More actions · Answer 4

No replies 🙁

Is there anyone aware of the solution to the problem???

havek-703864 Grasshopper Points: 13 More actions · Answer 5

Temp tables are not visible to the user but to sistem only (as far as i know). Therefore you will never see the temp table in a drop down list or in enterprise manager or the like. You really have two options: 1. do as you have already been suggested 2: create a table and drop it as soon as you are done with it.

The temp table you say you have seen others use is most probably not a #temp table (virtual) but a permanent table (on disc) that is then dropped. Some people refer to temp tables as #temp tables because they erase them immediately but, technically speaking, they are permanent (on disc) tables that do not permanently (time wise) reside on the disc. Either way, they use the term loosely.

One more thing, #temp tables can not be used across SQL tasks in a dts. Again, you currently have two options that you can use.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

If you want to use temp table try using ## instead of #.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

nikhil8960 Right there with Babe Points: 746 More actions · Answer 7

Thanks to all for helping. I am done with the issue. 🙂