Best practice error handling

Question

I’m re-writing our SQL 2014 SSIS packages in SQL 2016.  The main purpose of the re-write is to add additional error handling and intelligence to the packages.  I’m looking for a little “best practice” guidance.

First I’ll give a brief explanation of our packages.  They are written to process files we receive on our FTP site.  We have a main “wrapper” (parent) package that does things like: copies the package to a working directory, unzip the file if necessary and then copies it to an archive directory.  When the file is ready for processing, the parent package calls a sub-package (child) that does the cleansing/importing of the file, then calls a stored proc to do the final handling of the data.

I want to record errors as they occur, at whatever point they occur at, and gracefully exit the package.  This means I need to write a record to a table, handled with an Exec SQL Task that calls a sproc, and then delete the file from the working directory, then send an email to people based on where the package failed.  I’m wondering if the parent package should have one OnError event at the main package level or if it should have a separate OnError event for each step.  I will do the same thing no matter where the error occurs – call the sproc to insert a record into the table.  If the error occurs in the child package I also need to call the same sproc to insert a record into the table, but I also need the error to bubble up to the parent package so I know an error occurred.  If an error occurs in the parent or the child, as the very last step(s) I need to delete the file I was processing, then call another sproc that is going to determine where the error occurred and whom to email.  I’m not sure of the best way to accomplish all of this so I’m looking for suggestions.

I was thinking of having one OnError event at the parent package, and one at the child package.  I can handle all the errors that way just fine, but I can’t handle the final cleanup/email step.  I also don't know how to get the child package to tell the main package that an error has occurred.

Any help is appreciated.  Thanks in advance.

---

André

Answer 1

Hi André,

in your case having the error logging in each package makes more sense, it will be just easier controlled.

You can log with minimum effort to a table and the parent package can interrogate it for error messages.

The "best practice" if it exists at all, which I doubt, as in software it is always "depends", would be not to place all the logging into one entity, even better, take as mach of it out of the processors as possible. As a result you get a leaner setup with separation of concerns, faster workload processing, the logging, ideally, should be async.

I.e. it is common to write an additional app that would be involved in orchestration of your ETL, reporting, and recovery.

---

Arthur

MyBlog

Twitter

Answer 2

Thanks Arthur.  I appreciate your response and have a follow up for you.

If the child package has an error, will the parent package be aware of it? 

I'm struggling with the conceptual part of how the errors fire.  Assume I have only logging at the package level, and not each executable.  If an error happens in the parent package, my OnError event will fire to insert a record in my table via an Execute SQL Task. I can then have another Execute SQL Task that does the cleanup work necessary, both in the OnError event. Great.  However, if the error occurs in the child package and calls its own OnError Execute SQL Task and inserts a record into the table, then returns control to the parent, what happens?  I don't want it to insert another record into the table, but I do want it to do the cleanup. 

I like the concept of one error handling place in each package as it makes manageability a lot easier.  And I'll use SQL to send the error messages as I have to determine who gets the email, and it will be a lot easier to do in SQL. That part I'm good with.  Thanks

Andre

---

André

Answer 3

I have a very similar setup to the one you are describing.

Yes, the error in the child package will bubble up to the parent.

A send mail task in the On Error at the parent package level is what I am using, but I am not changing the message based on the error or where the error occurred.

One issue that tripped me up when logging the error to the log table via the stored procedure was the @[System::ErrorDescription] variable may contain Single quotes which throws of the syntax of the stored procedure call. I am using an expression to set the store procedure call.  

"execute [usp_ErrorLog] 
  ,@ErrorNum="+(DT_WSTR,60) @[System::ErrorCode]+"
  ,@ErrorMsg='Error : "+(DT_WSTR,60) @[System::ErrorCode] + " : " + SUBSTRING(REPLACE(@[System::ErrorDescription],"'"," "),1,956) + "'
  

Answer 4

HI André,

the errors from the child package bubble up to the parent, and stop the execution by default.

---

Arthur

MyBlog

Twitter

Answer 5

Thank you both.  I'll get back to you if I have further questions.

---

André

Answer 6

Hi andremg,

How is it going on? If no further question, please mark the replies making sense to close this thread.
 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

I'd say put the error handling in the same package that put the process in to that state. That is, if a child process did something to a file (moved it, created a new file etc) then the child package error handling should clean it. If the parent package is responsible for all the file movement etc, then the parent package should do the cleaning.

As for errors, they definitely bubble up... just try and stop them! (Propagate only works within a package, it doesn't stop the error returning to the parent package - at least in 2012 anyway) so you wouldn't need the child package to handle these unless you wanted to record detail that the parent doesn't know of - while the parent will be aware the child has failed, you can't actually pass anything back to the parent

Answer 8

Hi Ryan and Eric.  Unfortunately I've been a little side-tracked on this project lately but will begin back to it tomorrow.  I'll close it for now and if i have further questions I'll reply back later.  Thank you for your help.

---

André

Answer 9

Arthur,

A long delay, but I'm finally back on this project. 

Since you say that errors bubble up to the parent, is it even necessary to have any error handling in the child package?  I was going to basically have the same exact error handling, where an Exec SQL task sends SQL the error, which will store the error in a table and send out appropriate emails.  However, I don't want to get multiple copies of the emails, or have multiple records of the same exact error stored in the table.  If the child package's errors bubble up to the parent, isn't it sufficient to have the parent package do all the error handling?

Thank you again.

---

André

Answer 10

Hi André,

The email task you can put only where you want the emails to be sent from.

I guess you are after some sort of a centralized error handling in form of sending emails. 

Then you can potentially utilize the Event Handlers.

In the Child packages inhibit it using this article as reference, and then handle the errors in the Parent by sending an email from there.

---

Arthur

MyBlog

Twitter

Answer 11

There won't be an email task.  The Exec SQL task calls a stored proc, and the proc has the logic to decide who to send emails to - and dbmail will handle it rather than SSIS.

I guess I just need to know if the child package needs any error handling of its own, or if having it in the parent will suffice.  I really don't care if the error occurred in the parent or child; I just need to know what it was and pass System::ErrorDescription to my sproc - and only do it once.

---

André