Global temp tables are not created through SQL TASK control.....

Question

Global temp tables are not created through SQL TASK control.....

nithiyanandam-447806

Ten Centuries

Points: 1197
More actions
January 8, 2012 at 9:42 am

#253011

Hi,
I am new in SSIS.
I am trying to create a Global Temporary table (##) using SQL TASK in a package.
In this SQL TASK, the SQL statement is looks like create table ##table........
and the connection type is OLEDB
and the connection manager is pointed in TEMPDB.
After this task, I insert value the created ##table.
when I executed the package the SQLTASK ran successfully.
but the insert fails due to "Invalid object in the database".
Please help on this.
Thanks...
Yours,
Nithiyanandam.s

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Admittedly, I'm no SSIS expert but I believe that the SQL Task is being executed as a separate session. When the SQL Task exits, it ends the session which ends the availablility of the global temp table because no other session is using it.

That, notwithstanding, someone may have sussed this type of problem in the past and, if nothing else, I've at least "bumped" your post back to the top of the list. 🙂

As a bit of a sidebar, I'd be real careful about using Global Temp tables... you may have a heck of a surprise if two packages try to create the same named table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 2

Create physical tables to mimic the operation that you would want for the temporary objects. You can always drop the objects when no longer required.

Raunak J

joe.heym Valued Member Points: 55 More actions · Answer 3

In your Connection properties set:

RetainSameConnection = True

In associated Data Flow Task you may need to set property ValidateExternalMetaData = False

See the following for explanation and walkthrough:

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 4

joe.heym (1/9/2012)
In your Connection properties set:
RetainSameConnection = True
In associated Data Flow Task you may need to set property ValidateExternalMetaData = False
See the following for explanation and walkthrough:

Temporary and global tables are session specific. They should be used for only intermediate purpose only. Avoid such practice to place data in such objects.

Raunak J

kavinithi Old Hand Points: 397 More actions · Answer 5

HI,

Check your userid and password for the connection string.

Your user account may haven't the permission on this....

Check this. this may help you

kramaswamy SSCoach Points: 18181 More actions · Answer 6

Gonna have to agree with the others - just don't use the global temp table. Use a real table, and run a TRUNCATE TABLE at the start of your task if you need to eliminate the values within before the task runs.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

kramaswamy (1/9/2012)
Gonna have to agree with the others - just don't use the global temp table. Use a real table, and run a TRUNCATE TABLE at the start of your task if you need to eliminate the values within before the task runs.

I'll have to disagree with that, as well. If you have two tasks that use the same table or you need to run the same task in a concurrent fashion, such truncation would prevent such concurrent runs. You either need to dynamically create uniquely named tables or create rows with unique batch IDs and use DELETEs instead of TRUNCATEs at the end of a given run.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nithiyanandam-447806 Ten Centuries Points: 1197 More actions · Answer 8

Hi All,

Thanks for your help.

I followed all your instruction and set the "RetainSameConnection = 'TRUE' ".

My Problem solved. Thanks very much.

Thanks,

Nithiyanandam.S