SSIS onTaskfailed/OnErrorfailed Events, not able to capture recent errors

Question

SSIS event handler onerrorfail or ontaskfailed both failed to capture recent error messages. Situation - 1. Parent package calls child package. 2.Parent has no errors but child package has errors and i'm trying to capture those errors.

Select TOP 1 
  A.package_name
  ,A.[Message]
FROM SSISDB.[catalog].[Event_messages] A
Where A.execution_path like  '%Template%'   
AND A.Event_name = 'OnError'
Order by A.Message_time desc

Above SQL with OnError(event handler) - Retrieves previous error record. Above SQL with onTaskfailed(event handler) - Retrieves previous error record and most recent error record as well so email getting triggered twice. In order stop multiple emails, I have put in Counter variable. Counter variable resolved the email issue but it's giving previous error record not the recent errors. There is one error to be precise, i have no idea why it is giving past error. I have used date filters as well nothing is working except above SQL although i have to deal with multiple emails.

What is wrong with above SQL(basic & straight forward), Why it is such a problem to get most recent record? Better way to handle this?

Answer 1

I think the better way is that you use "OnError" event handler in "Event Handlers" tab for the exact data flow or control flow (Whatever you have in your child package) in your child package. Because the package has failed is child package and you want to capture its error. So, You can add one "Execute SQL Task" in your OnError and use the "System::ErrorCode" and "System::ErrorDescription" (They are system variables) for inserting in one log table that you like. your insert command should be something like this "insert into [table_name] ([CodeColumn], [DscColumn]) values (?,?);" and you can use those system variables to catch your error. I offer you use one datetime column too for understanding the errors better. I hope it works for you.