Search code examples
sql-serverstored-proceduresssiscursors

Execute SQL Task continue execution after completion of Stored Procedure

I have stored procedure which i am trying to execute in SSIS using Execute SQL Task. This stored procedure involves Cursor and last step is De-allocate Cursor. I observe that even after the execution of stored procedure is completed Execute SQL task doesn't complete and halts for long time before moving to next step. To test this i have added insert stmt as last line in stored procedure with GETDATE() as one column value. It shows STP execution completed almost 2 hours prior to Execute SQL Task completion. Any idea if Execute SQL Task is running some additional steps along with required stored procedure

Solution

  • Solved :) this was basically because i was using few Print statements in SQL stored procedures and as i was executing these stored procs from SSIS it might be creating some buffers which was being cleared after execution. I commented Print stmts in my stored proc and execution time in SSIS is same as execution time in SSMS.

    Thanks for your time in helping me.

    Regards