SSIS Serialized OnError Tasks logging error on SQL Server and sending email

Question

I do have a quite big project in SSIS, with multiple packages, and some MASTER packages using them.

I want to find the best method to log errors, and send an email on failure.

For that here are some of my constraints :

• Log errors on SQL Server
• Send an email with information corresponding to the package name and the error description
• Serialize the email part, not having to modify this email task in all my packages by hand

To do that, I thought about creating a package part, with a C# script where I send the email.

My biggest problem is to retrieve the ErrorDescription.

I tried the followings :

• Get the variable ErrorDescription in the Event Handlers of a package. But when I try to retrive it in the C# script of the Email package part, I can't do it (string ErrorDesc = Dts.Variables["$User::ErrorDescription"].Value.ToString())
• Get the last rows of the table dbo.sysssislog with a SQL task before my C# script, registering the result in a variable. But since I use a Project Connection Manager I need to encrypt the package part like the rest of the project (EncryptSensitiveWithPassword), and then I can't access to the variable of my project Dts.Variables["$Project::SMTP_Credential_Users"].Value.ToString() (which I could access with an email package part not encrypted.

What recommendation do you have in order to log errors and send emails with error content in a serializable way ?