Execute SQL Task hangs...

Hi,

I'm having a problem with one 'Execute SQL Task' calling a stored proc...

The problem is that once the stored proc has finished executing after 10 mins (I know it has thanks to an audit log table entry ), the SSIS task carries on hanging for another 20mins before it gets the task execution result and moves to the following task.

I tried:

- adding/removing a return statement at the end of the stored proc. It doesn't solve the problem

- running the package without debugging and outside visual studio, and I'm still getting the same problem. so it's nothing to do with the debugger...

- tried various settings for the transaction and isolation settings... not getting any better

- changed the stored proc so that it processes less data. Execution time goes down to a couple of minutes and the SSIS tasks only hangs for 2 mins... better but not really helping...

Any clues? I'm new to SSIS so I must have certainly missed something!

Thanks.

Have you used SQL Server profiler to check if the SP has actually finished? I never have seen that problem before and I cannot think it is a problem in SSIS.|||Check for blocking, plenty of info in Books Online. I'm old school so try sp_who2 as start.|||

sp_who2 was another thing I tried... there was no runnable process, no lock etc..

anyway, I finally managed to sort this out... I changed the connection type from OLE DB to ADO.NET and it did the trick...

I have to say it's rather puzzling... I did check the stored proc in case there was something unusual... nocount is set to on, there is not print statement, no select statement, just one return at the end of the stored proc...

when running in visual studio, the debugger process runs at full cpu but doesn't do anything apart from eating more and more memory...

anyway, it's sorted for the time being...

Thanks for your suggestions.