Execute SQL Task continue execution after completion of Stored Procedure

0
votes

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

1
sql-serverstored-proceduresssiscursors
Why do you assume that SSIS or the Execute SQL task ar at fault? Using cursors is a very strong smell - there are very, very, very few things that require a cursor. The only valid scenario, batch processing, is addressed by an SSIS datalfow. Most likely your stored procedure is not ending when you think it is, because the cursor takes far longer to complete than an equivalent SQL statement. Worst case, using a Cursor ends up locking the entire table for hours, resulting in livelocking with other statements - Panagiotis Kanavos
Can you post your code or tell us what you are trying to do. It will give us a better idea of what is happening and maybe find a better solution. - Wes Palmer
Also look into running profiler rather than just using an insert statement at the end of the SP> - Joe C
@panagiotis: :) i am not considering any task at fault here, just trying to identify what happens after last line of code. My scenario for using Cursor is as below: I have say 10 records each of them needs to run through different set of rules for checking on correctness. This makes me run through one record at a time, hence cursor. I have used Hint for Row level locking on table so i assume it wont lock entire table. Happy to learn if you have any better way to address this. thanks for your help - Cyfa
It's impossible to answer without the queries themselves. Anyway, you don't need a cursor even for that. You can run 1 query for each rule knowing that the optimizer will bypass. You could convert the rules to functions and just join with the data. Or you could create a truth table containing entries for all rules and join them with the data - Panagiotis Kanavos

1 Answers

1
votes

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