Executing the StoredProcedure with ExecuteSQL task in debug mode is never ending, but when executing same SP directly from sql (Management Studio) completes in less than 3min. Please suggest what could be my mistake.

what is your connection type?http://www.rad.pasfu.com

OLE DB

Are you running this SP off your local PC? It could be that this SP is buffering into your PC which in turn is a less than optimal machine in terms of resources, memory in particular. Arthur My Blog

I am not running it from local machine. Actually running SSIS package on a server.

did you tried to execute it out of BIDS? with DTEXEC or DTEXECUI ? what was result?http://www.rad.pasfu.com

Because of access restrictions I can't run using DTEXEC. Tried executing other SP's from same Package and ran successfully. Is there any property I am missing for Execute SQL Task.

Maybe this is debugging environment issue, that was the reason that I asked to use DTEXEC or DTEXECUI. what is the result at all? does the stored procedure executed successfully? do you have other tasks after execute sql task? how much times you waits to finish the package ?http://www.rad.pasfu.com

I understand as debugging env issue, but why does one specific sp never complete. There is no result set returning from SP. Just executing some insert statements using cursor in SP. There are other tasks after SQL task which never get to run because Exec SQL task takes forever. I stopped debugging after 8hrs. (SP from direct sql took less than 3min)

I understand as debugging env issue, but why does one specific sp never complete. There is no result set returning from SP. Just executing some insert statements using cursor in SP. There are other tasks after SQL task which never get to run because Exec SQL task takes forever. I stopped debugging after 8hrs. (SP from direct sql took less than 3min) Cursors are evil. I guess you use SQL Agent to run the package and the Agent is an async process, thus combined with this cursor it messes with the memory [threads] up. Revise your SP to not use the cursor. You will gain in speed and reliability.Arthur My Blog

I know Cursors are evil but these SP's are third party application upgrade scripts on which I have no control to modify. Need to run with out any changes.

I know Cursors are evil but these SP's are third party application upgrade scripts on which I have no control to modify. Need to run with out any changes. this SP will reduce your performance awfully, it is better to change SP in my view also. http://www.rad.pasfu.com

Your other option will be then running outside the SQL Agent Arthur My Blog

Wonder if this is related to the security role that is used to execute the package. Not so sure, but it might worth a check.Daniel Cai | http://danielcai.blogspot.com