stored procedure not working in Execute Sql Tak

Question

stored procedure not working in Execute Sql Tak

Viewing 3 posts - 1 through 4 (of 4 total)

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

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

The reason why it works in SSMS is because your connection is still open, so you can still access the global temp table ##temp.

In SSIS however, the connection manager does not create a single connection, but it creates different connections for the various tasks that need those connections. You can see the connection manager as a factory that creates connections. What has this to do with temp tables? When you create the temp table in one task, the connection will already be closed when you arrive at another task. This has the consequence that the temp table is destroyed. If you do not want this, you must set the RetainSameConnection property of your connection manager to TRUE. This will ensure that the connection manager only creates one connection, so that your temp table will still exist when you need it.

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

preensheen Ten Centuries Points: 1362 More actions · Answer 2

da-zero (10/26/2010)
The reason why it works in SSMS is because your connection is still open, so you can still access the global temp table ##temp.
In SSIS however, the connection manager does not create a single connection, but it creates different connections for the various tasks that need those connections. You can see the connection manager as a factory that creates connections. What has this to do with temp tables? When you create the temp table in one task, the connection will already be closed when you arrive at another task. This has the consequence that the temp table is destroyed. If you do not want this, you must set the RetainSameConnection property of your connection manager to TRUE. This will ensure that the connection manager only creates one connection, so that your temp table will still exist when you need it.

OMG..Thanks a lot, I was struggling hard.

It worked.:-):-):-):-)

Thanks,

preensheen

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

Glad that it worked out.

We all have been struggling at some point with SSIS and temp tables...

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