SSIS Error propagation issues

  • 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

     

     

  • 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!

  • 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.

    sourceexecutionid
    Update Assignment Current Keys70BFC971-B8A2-447F-9BDE-40E93AD8EA4E<==offending step
    FACT_AssignmentServiceTimes70BFC971-B8A2-447F-9BDE-40E93AD8EA4E<== 3rd package in chain. Has the offending step
    Load Assignment Service Time Fact70BFC971-B8A2-447F-9BDE-40E93AD8EA4E
    CTL_FactTableLoad70BFC971-B8A2-447F-9BDE-40E93AD8EA4E
    Update Assignment Current Keys2A166CFA-3262-47AE-A312-E53D74181E4B
    FACT_AssignmentServiceTimes2A166CFA-3262-47AE-A312-E53D74181E4B
    Load Assignment Service Time FactAE0F3FE7-AC11-45C5-8E6D-580D575B438D
    CTL_FactTableLoadAE0F3FE7-AC11-45C5-8E6D-580D575B438D<== 2nd package in chain
    Load Fact TablesAE0F3FE7-AC11-45C5-8E6D-580D575B438D
    Load Data Warehouse and Update Config InfoAE0F3FE7-AC11-45C5-8E6D-580D575B438D
    For each active not running applicationAE0F3FE7-AC11-45C5-8E6D-580D575B438D
    CTL_DataWarehouseLoadAE0F3FE7-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