[font="Courier New"]Here's the way I send out a single error message (error message 1) to my pager.
1) Set up a variable at the package level as an error counter; such as "ErrorCounter" as Int32
2) Add a SQL Task to the OnError event handler at the package level.
3) Connect it to your SQL Server database and use direct input with the code "select ? + 1 as counter" to increment
4) In Parameter Mapping on the task, use the "User::ErrorCounter" variable as Input as LONG for Param1 with size -1
5) In Result Set, use "counter" as Result Set and "User::ErrorCounter" as the variable
6) Add a Send Email task to the event handler and connect the SQL task to it with a Precedence Constraint
7) Set the Precedence Constraint to Evaluate an "Expression" and set the expression to "@ErrorCounter==1"
8) add the email info to send the email (in my case to my pager)
9) Add two Expressions to your Send Mail task:
(1)MessageSource with formula = "Error No.: " + (DT_STR, 10, 1252) @[User::ErrorCounter] + "\rError Code: " + (DT_STR, 25, 1252) @[System::ErrorCode] + "\rError Description: " + @[System::ErrorDescription]
(2)Subject with formula = "Error No.: " + (DT_STR, 5, 1252) @[User::ErrorCounter] + " in SSIS Package: " + TRIM( @[System::PackageName] )
I also send out all the error messages to my email. To do this, copy the Send Email Task and attach it to the SQL Task
with a normal Precedence Constraint (green line) and the emails will flow in one at a time per error message. They
are usually not that helpful, but sometimes they can be. I don't mind having all the emails to look at when I get paged.
I seldom get paged, but when I do, I don't mind looking at the emails to find the issue(s). I also have not yet had
to deal with logging errors in SSIS using my method.[/font]