October 26, 2010 at 9:59 am
Hi All,
I have stored procedure, When I run the SP in SSMS it works fine...but when I am trying to execute the same in execute sql task it is not... I am not able to understand the reason why?...I have set the result set to None. The SP runs even when I build the Query and it also shows green when I execute the task.
....But have a problem...
Since,
I am storing my result into a Globaltemp table in the SP, AND when I say Select * ##temp this works in SSMS AFTER i exec my SP.
But after i run my execute sql task and try to run the same select * ##temp..it doesn't show up any result instead it says we don't have any tempdb my tht name.
Plesae help me...i am totally lost thinkign where am I making the mistake.
October 26, 2010 at 11:04 am
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
October 26, 2010 at 11:38 am
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
October 27, 2010 at 1:34 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply