asked
SSIS Temp table created in one Execute SQL Task in not available in next SQL Task
Hi Frensz, Temp table created in one Execute SQL Task of SSIS package in not available in the following SQL Task. script used is create table tempdb.dbo.test (x int) Any guidenance in this context? Aniother note is its working in BIDS but not in production server.
Temporary tables are generally only available in the session that creates them. Details here
http://msdn.microsoft.com/en-us/library/ms174979.aspx. Creating a global temporary table is not considered best practice. If you need the data in a table for the duration of your SSIS/DTS package then why not have a 'real' table in the database specifically for that use? You can easily truncate it when you are done with it if leaving the data in place is a concern.
OP not really describing a temporary table, more a temporary table created in tempdb.
We can use the temporary tables in another Execute SQL Task . Go to the Connection which you have created in Connection Managers and in which connection you want the temporary table to be created. Go to that Connection Properties and Change the Property RetainSameConnection = True . It will allow you to access the temporary objects to the next task.
