August 2, 2022 at 11:17 am
I'm trying to add some more robust error handling to my packages.
I'm adding Try/Catch to all my Script tasks and then using an OnError event handler on the package to log any errors to a table and send an email alert.
I have a single "ErrorMessage" variable that can hold 3 types of error. It can record manually trapped business logic errors where I check and set this variable based on a condition I'm checking. If a script task errors and hits a Catch, I'm setting this variable to the Exception message thrown. Finally if the variable hasn't been manually set, and the source task wasn't a Script Task, I'm setting the variable to the "System::ErrorDescription" value.
This works well but I now have a situation where on error sends the email from my OnError event, and also the calling SQL Job sends a job fail notification to the job operators.
Now I need the job fail alerts for when the job fails on other non SSIS steps, but if the job fails on the SSIS step, I want to suppress the job fail email alert as the package will trap and send an email, and I don't want a redundant email alert especially as the Job alert doesn't have a verbose error message which my manually sent alerts do have..
So my question: Does the OnError alert in an SSIS package always cause the calling job to fail or is there any way inside the on error event handler to show a package success so my event handler handles all the SSIS error logging and alerting, and the SQL Job doesn't fail when this happens?
August 2, 2022 at 2:53 pm
What do you want the job to so if the SSIS package fails? You can set the properties of FailPackageOnFailure and FailParentOnFailure to false for all objects within the package. This will enable the onError event handler to fire and the package will report success. This sounds like what you need, but it will mean your job goes to the next step. If you want the job to go to the next step you can set the job step to go to next step on failure, without touching the package. Is that what you need? or do you need the job to stop, but not allow the SQL Agent to send an email? If you want it to stop you could add a final step the the job that does nothing (e.g. Step 25), and set the ssis job step to go to step 25 on failure. That way the job progress will be stopped, SQL Agent will not report failure, and the ssis package event handler will send the notification.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply