How to capture runtime errors when executing stored procedure

[newtechy]

I have created 2 SQL tasks in a DTS package workflow. The first task selects/deletes from a table in database X one row at a time. The 2nd sql task invokes a stored procedure in database Y. Between the 2 SQL tasks, I have an Active X Script Task that begins the looping process, and at the end of the workflow, I have another Active X Script task that reinitiates the first SQL task. The workflow continues until all rows from the table in DB X have been exhausted. When all the rows in the table on DB X have processed, the package completes. The workflow works fine until an error is encountered in the stored procedure in DB Y. To date, the stored procedure has no error handling logic. Is there a way for the workflow to capture errors and resume processing to the next available row in the table on DB X?? Please advise...

 

[SQLSister]

Well your whole process seems odd to me. I never process one record at a time if I can help it. Usually I prefer to bring the data into a holding table, clean up any possible error problems in the data and then run the transformation on the whole table at once. Processing one record at a time is slow and should be avoided with SQL Server.