question

pvsrinivasrao avatar image
pvsrinivasrao 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.
sql-server-2008ssisdts
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Have you tried creating the table in a different database?
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OP not really describing a temporary table, more a temporary table created in tempdb.
0 Likes 0 ·
Kirman avatar image
Kirman answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.