TEMP tables

[manmaria]

How to use TEMP tables? I guess the following query should create a temp table ##blah2

select col1,col2,col3
into ##blah2
from blah1

I can run this query in Manament Studio but I am not EVEN able to execute in IS?

Thanks

 

[Catadmin]

You need to put the query into an Execute SQL Task in order for it to work.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[manmaria]

Thanks. I did. In the First Exec Sql query I was selecting into ##blah2 and in the second Exec Sql query I was using the blah2 table to insert into another table. But no success. Is this temp table avaialbel for the next task? or move the values into variables?

Thanks.

 

[Catadmin]

Technically, the ##blah table should be available globally until you delete it. However, I'm wondering if the new improved functionality of SSIS forces you to send data to package variables to carry it across tasks or set up a more permanent type of "temp" table to do what you're trying to do.

If the ## type of temp table isn't working, the simplest solution is to try the more permanent version and then drop the table when you're done with it. I'm currently experimenting with moving a result set into a package variable, but I'm getting the error " failed with the following error: "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly." so I can't tell you if it'll work or not.

If I get this variable thing to work, I'll let you know.

Sorry I can't help more.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"