sql server dts: Temp tables in DTS -- sql server dts

RE: Temp tables in DTS

RLoski
3/6/2007 9:10:14 AM

Yes.
You build the temp table in an ExecuteSQL statement. Then you can reference
that table in other ExecuteSQL statements that use that connection or as the
source/target of a tranformation. I have even used temp tables in order to
loop through a set (load the temp table, then pop one record off to process).
Then you drop the temp table at the end (or it will drop when you exit the
package).

--
Russel Loski, MCSD.Net

[quoted text, click to view]

RE: Temp tables in DTS

Kolos
3/6/2007 1:29:35 PM

Thanks Russel. Do I have to put the result in a global variable and later
retrieve it by ActiveX scripting? That seems to me a bit blunt. Is there an
easier way?

Thanks a thousand.

Kolos

[quoted text, click to view]

RE: Temp tables in DTS

RLoski
3/6/2007 3:51:08 PM

You refer to the temp table just like you would any other table. Unless you
use a global temp table (##temp), you won't be able to see the table on any
other connection than the one that you created the temp table on. You can do
any query that you normally do on a table (delete, update, select) in
ExecuteSQL task. You can use the temp table as a source or target for a
transformation.

--
Russel Loski, MCSD.Net

[quoted text, click to view]

RE: Temp tables in DTS

Kolos
3/7/2007 6:23:07 AM

Sorry to be a pain, but I have one more difficulty.

My goal is to export data from SQL server to an Excel table.
1st step is to create the file and it works fine.
2nd step is the Execute SQL Task where I put some data into table ##temp.
3rd step the Transform data task between the SQL connection and Excel
connection does not know about my ##temp table even if I executed that step
directly before editing the task. So I can't refer to the temp table.

What do I do badly? Please help.

Thanks.

Kolos

[quoted text, click to view]

RE: Temp tables in DTS

RLoski
3/7/2007 7:05:18 AM

There are two stages: developing package and running package. When
developing, you won't see the temp table. When running it should be there.

To get this to work, I create a table with the same structure as my temp
table. Then I create a transform from that "real" table to the excel
spreadsheet.

Next, I use disconnected edit (you find that on the left most menu). I find
the tranform in the tasks. I find either the source sql statement or source
object and replace the name of the "real" table with the temp table name. If
the temp table was created on the same connection as the source table of your
transform, it should work.
--
Russel Loski, MCSD.Net

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.