Running a SP that contains temp table throws error

Question

Running a SP that contains temp table throws error

Mr J

Hall of Fame

Points: 3595
More actions
March 1, 2010 at 5:00 pm

#139105

Hello friends,
I'm having an issue, I've created a SSIS package which basically runs a few SP individually and splits the output into an access db. Now the issues i have is that if the SP creates a temp table the SSIS data reader fails, obliviously it runs without any issue as a query in SQL2005. Is this a know issues?? Can anyone suggest or point me to anywhere that might shed some light on this?
Cheers
Mr J
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!

Viewing 15 posts - 1 through 15 (of 16 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

SSIS has trouble with temp tables. There is a setting on the connection that *may* help. That setting is "RetainSameConnection", you can set it true and it might help but its sketchy..

CEWII

Mr J Hall of Fame Points: 3595 More actions · Answer 2

Thanks dude for the advice but still hasn't help. Free feel to suggest anythin else if you think it might help, I'm bit of a dead end with this one!

Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Create a persisted table in tempdb like tempdb.dbo.yourtemptablename and clean it out when you are done.

E.

Mr J Hall of Fame Points: 3595 More actions · Answer 4

Yeah i guess I'm hoping to avoid this because it means re-writing a lot of SP and I'm feeling particularly lazy today :rolleyes:. Its quite annoyin the SSIS has issues with #tables i mean its a basic function!!

Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Find - Replace, should be REALLY easy..

If you understand why temp tables are a problem it might make more sense.

For performance reasons SSIS wants to have several connections open, so once you move out of a task (such as an EXEC SQL task) you cannot be sure what connection you are necessarily going to be on. It is the same issue with transactions where you have multiple paths into a single table. What happens is that when you have a data-flow task that has multiple paths and the destination is the same table and transactions are enabled you will often get blocked. The reason is that you have two seperate connections to the server and they are in seperate transaction contexts.

Does this make any sense?

CEWII

Mr J Hall of Fame Points: 3595 More actions · Answer 6

Yeah thanks for that i understand. Does it use multiple connections for queries too? you would think this is done in a singular connection.

Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

I think I need to qualify this answer..

A single query will only be run against a single connection. What that means is that for something like an EXEC SQL task will only generate a single connection, but if you have mutiple sources in a data-flow task then each will likely open their own connection. It becomes less clear in Script Task, because you can access all the connection objects or build an entirely seperate ont on your own..

CEWII

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 8

When I have to use temp tables in my packages, I always use global temp tables, like ##temp and with the property RetainSameConnection set to True (as said before by Elliot W).

This normally always works with my packages.

In addition to the explanation of Elliot W regarding temp tables:

it helps to see the connection managers not as a connection per se, but as a connection factory that creates (possible) multiple connections between the source and the destination.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

Elliott Whitlow

SSC Guru

Points: 102402

March 2, 2010 at 9:19 am

#1127181

I think that is a good analogy..

CEWII

Mr J Hall of Fame Points: 3595 More actions · Answer 10

Thanks heaps guys I've learn alot today 🙂

Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!

Mr J Hall of Fame Points: 3595 More actions · Answer 11

Hello Friends its me again,

I've done what you have suggested using global temp tables and using RetainSameConnection, but i seem to be receiving another perplexing error

ExecuteReader requires an open and available Connection. The connection's current state is closed. (Microsoft.SqlServer.DTSPipelineWrap)

this only happens once i enable RetainSameConnection, is their something that i may have missed?

Thanks again Mr J 🙂

Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

You could try simply capturing the output from your first stored proc to an SSIS variable, and pass it to the second stored proc as a parameter.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 13

Elliott Whitlow

SSC Guru

Points: 102402

March 2, 2010 at 7:40 pm

#1127494

A whole resultset?

CEWII

Mr J Hall of Fame Points: 3595 More actions · Answer 14

Hmmm i would be interested in learning more about capturing the output from the first stored proc to an SSIS variable. 🙂

Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!