June 15, 2017 at 3:02 am
I have an SSIS package for the overnight data warehouse load. It comprises a master package that calls a series of child packages. These in turn call stored procs to do the various staging and fact loads. The whole thing is run from an agent job. Part of the error handing is a stored proc that emails the Service Desk to tell them to call me and which also stops the Agent Job running. The reason I want the job to stop is that our data warehouse is used by the customer to load their data warehouse (because politics) and their data warehouse load takes hours. If dodgy data or an empty table was loaded (both of which have happened in the pre-error handling era) it meant a lengthy delay in the morning while things were reloaded. Stopping the job and kicking it off again manually when the error had been dealt with means a shorter\no noticeable delay in the morning.
The paradox I'm facing is that each of the stored procs is wrapped in a TRY\CATCH to pick up any T-SQL errors. The CATCH block calls the error handling proc and logs the error details, However, because the CATCH block has successfully caught, the Execute SQL Task in SSIS is considered to have succeeded. Never mind the fact no rows were loaded, the error handling worked as it should so as far as SSIS is concerned everything is hunkydory. This means that the checkpoint file has the errant task 'marked as a success' and so it it's not re-run when the job is restarted. Is there any way of marking the SSIS task as failed from the stored proc even though it has technically succeeded?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 15, 2017 at 6:06 am
Neil Burton - Thursday, June 15, 2017 3:02 AMI have an SSIS package for the overnight data warehouse load. It comprises a master package that calls a series of child packages. These in turn call stored procs to do the various staging and fact loads. The whole thing is run from an agent job. Part of the error handing is a stored proc that emails the Service Desk to tell them to call me and which also stops the Agent Job running. The reason I want the job to stop is that our data warehouse is used by the customer to load their data warehouse (because politics) and their data warehouse load takes hours. If dodgy data or an empty table was loaded (both of which have happened in the pre-error handling era) it meant a lengthy delay in the morning while things were reloaded. Stopping the job and kicking it off again manually when the error had been dealt with means a shorter\no noticeable delay in the morning.The paradox I'm facing is that each of the stored procs is wrapped in a TRY\CATCH to pick up any T-SQL errors. The CATCH block calls the error handling proc and logs the error details, However, because the CATCH block has successfully caught, the Execute SQL Task in SSIS is considered to have succeeded. Never mind the fact no rows were loaded, the error handling worked as it should so as far as SSIS is concerned everything is hunkydory. This means that the checkpoint file has the errant task 'marked as a success' and so it it's not re-run when the job is restarted. Is there any way of marking the SSIS task as failed from the stored proc even though it has technically succeeded?
Have you considered putting a THROW after your error-handling proc has been called?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 15, 2017 at 6:57 am
Does the T-SQL TRY/CATCH only suppress the errors?
😎
June 15, 2017 at 7:17 am
Eirikur Eiriksson - Thursday, June 15, 2017 6:57 AMDoes the T-SQL TRY/CATCH only suppress the errors?
😎
In what respect? It logs the error in a table then sends one email to the service desk who ring me to fix things and an email to me with the error details. It also stops the load. It doesn't do anything about the error itself I suppose.
Have you considered putting a THROW after your error-handling proc has been called?
No I hadn't. I've given it a go and it looks like it does exactly what I'm after. Thanks, Phil
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 15, 2017 at 7:33 am
Neil Burton - Thursday, June 15, 2017 7:17 AMEirikur Eiriksson - Thursday, June 15, 2017 6:57 AMDoes the T-SQL TRY/CATCH only suppress the errors?
😎In what respect? It logs the error in a table then sends one email to the service desk who ring me to fix things and an email to me with the error details. It also stops the load. It doesn't do anything about the error itself I suppose.
Have you considered putting a THROW after your error-handling proc has been called?
No I hadn't. I've given it a go and it looks like it does exactly what I'm after. Thanks, Phil
In the respect of not raising / throwing the error if the try/catch cannot "fix" the error, an execute sql task within SSIS would just be ignorant of the error and report success which seams to be the problem.
😎
June 15, 2017 at 7:42 am
Eirikur Eiriksson - Thursday, June 15, 2017 7:33 AMNeil Burton - Thursday, June 15, 2017 7:17 AMEirikur Eiriksson - Thursday, June 15, 2017 6:57 AMDoes the T-SQL TRY/CATCH only suppress the errors?
😎In what respect? It logs the error in a table then sends one email to the service desk who ring me to fix things and an email to me with the error details. It also stops the load. It doesn't do anything about the error itself I suppose.
Have you considered putting a THROW after your error-handling proc has been called?
No I hadn't. I've given it a go and it looks like it does exactly what I'm after. Thanks, PhilIn the respect of not raising / throwing the error if the try/catch cannot "fix" the error, an execute sql task within SSIS would just be ignorant of the error and report success which seams to be the problem.
😎
I see, so yes it does just suppress the error so SSIS doesn't see it. The addition of the THROW that Phil suggested fails the task but the emails are still sent and the job is still stopped. I'm going to do a bit more testing but it seems that it's as simple as adding that.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply