October 25, 2006 at 9:34 am
I have an SSIS solution which includes many packages. One of the packages is the set as the startup object. In this package I've configured a Send Mail Task for the OnError event. I want the Send Mail Task to include the names of the package and step causing errors.
I've included @[System:: PackageName] and @[System::SourceName] in the MessageSource of the Send Mail Task. Unfortunately the @[System:: PackageName] variable contains the name of the startup package, not the name of error-causing package. However, the @[System::SourceName] variable does contain the name of error-causing step.
I'll try to illustrate this issue with an example.
CTL_StartPackage.dtsx <== startup package with OnError handling.
DW_LoadAssignments.dtsx <== has a step called "Initialize work tables".
CTL_StartPackage calls DW_LoadAssignments.
The step "Initialize work tables" in DW_LoadAssignments errors out.
The OnError handler in CTL_StartPackage executes a Send Mail Task which includes the values for the variables @[System:: PackageName] ("CTL_StartPackage") and @[System::SourceName] ("Initialize work tables").
I really need to report the name of the failing package in the E-Mail, because I have several child packages which each have a step named "Initialize work tables". So if one of those steps fails I want to know which one.
Michael
October 26, 2006 at 7:48 am
What if you log errors to sql (sysdtslog90 table), then you could retrieve the data based on the executionid and include in your email... Good Luck!
October 26, 2006 at 11:48 am
I am logging errors to sysdtslog90. Unfortunately, it won't be able to help. Below is the contents of the table containing the error. Notice that there are 3 different executionid's.
In this example I want to report FACT_AssignmentServiceTimes as the failing package. But I have no way of finding package type sources in this table. Even if there was a way to only consider package type objects, I no way of knowing which package contains the offending task. The executionid for the offending package is different than that of the starting package.
| source | executionid | |
| Update Assignment Current Keys | 70BFC971-B8A2-447F-9BDE-40E93AD8EA4E | <==offending step |
| FACT_AssignmentServiceTimes | 70BFC971-B8A2-447F-9BDE-40E93AD8EA4E | <== 3rd package in chain. Has the offending step |
| Load Assignment Service Time Fact | 70BFC971-B8A2-447F-9BDE-40E93AD8EA4E | |
| CTL_FactTableLoad | 70BFC971-B8A2-447F-9BDE-40E93AD8EA4E | |
| Update Assignment Current Keys | 2A166CFA-3262-47AE-A312-E53D74181E4B | |
| FACT_AssignmentServiceTimes | 2A166CFA-3262-47AE-A312-E53D74181E4B | |
| Load Assignment Service Time Fact | AE0F3FE7-AC11-45C5-8E6D-580D575B438D | |
| CTL_FactTableLoad | AE0F3FE7-AC11-45C5-8E6D-580D575B438D | <== 2nd package in chain |
| Load Fact Tables | AE0F3FE7-AC11-45C5-8E6D-580D575B438D | |
| Load Data Warehouse and Update Config Info | AE0F3FE7-AC11-45C5-8E6D-580D575B438D | |
| For each active not running application | AE0F3FE7-AC11-45C5-8E6D-580D575B438D | |
| CTL_DataWarehouseLoad | AE0F3FE7-AC11-45C5-8E6D-580D575B438D | <==starting package |
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply