CODEWITHSUNDEEP
ssisparent-child
Brad
Brad

Reputation: 3601

SSIS when child package fails pass FULL error to parent package

I have a parent package that calls child packages. If the child package fails, the full error details are displayed for the child package but the parent package just says "Task Execute Package Failed".

How can I have the parent package obtain the full error message from the child package so I can properly grab the full error details in the parent package?

Upvotes: 1

Views: 3631

Answers (2)

Brad
Brad

Reputation: 3601

SOLUTION:

How to get Error Details from child packages.

This solution will take any error the child package has and pass the error message up to the parent package. Then the parent package will take the error it receives and post the full details in the parent package Execution Results.

NOTE: The logic is written so the child package can still be run by itself (not as a child package) without any errors from missing variable names in the parent package. Additionally if you run the child package from a parent package but do NOT want to capture the error from the child package just do not include the variable name or the OnError event handler and it will not cause any errors either for missing variables this way.

  1. Created an OnError Event Handler for the entire child package.

  2. Add a script task to the event handler.

    a. Pass it the variables in Read Only: System::ErrorDescription,System::SourceName,System::PackageName

    b. In the script task do this (comments should details what it is doing):

        // build our the error message
    string ErrorMessageToPassToParent = "Package Name:  " + Dts.Variables["System::PackageName"].Value.ToString() + Environment.NewLine + Environment.NewLine +
        "Step Failed On:  " + Dts.Variables["System::SourceName"].Value.ToString() + Environment.NewLine + Environment.NewLine +
        "Error Description:  " + Dts.Variables["System::ErrorDescription"].Value.ToString();



    // have to do this FIRST so you can access variable without passing it into the script task from SSIS tool box
    // populate collection of variables. This will include parent package variables.
    Variables vars = null;
    Dts.VariableDispenser.GetVariables(ref vars);


    // checks if this variable exists in parent first, and if so then will set it to the value of the child variable 
    //  (do this so if parent package does not have the variable it will not error out when trying to set a non-existent variable)
    if (Dts.VariableDispenser.Contains("OnError_ErrorDescription_FromChild") == true)
    {

        // Lock the to and from variables. 
        // parent variable
        Dts.VariableDispenser.LockForWrite("User::OnError_ErrorDescription_FromChild");

        // Need to call GetVariables again after locking them. Not sure why - perhaps to get a clean post-lock set of values.
        Dts.VariableDispenser.GetVariables(ref vars);

        // Set parentvar = childvar
        vars["User::OnError_ErrorDescription_FromChild"].Value = ErrorMessageToPassToParent;

        vars.Unlock();
    }

  3. In the parent package create a string variable: OnError_ErrorDescription_FromChild

  4. In the parent package create an OnError Event Handler for the entire package and add a script task to it. (Like you did with the child package above)

  5. In the script task pass the variable as read only: User::OnError_ErrorDescription_FromChild

  6. In the script task do the following:

        // get the variable from the parent package for the error
    string ErrorFromChildPackage = Dts.Variables["User::OnError_ErrorDescription_FromChild"].Value.ToString();

    // do a check if the value is empty or not (so we know if the error came from the child package or occurred in the parent package itself
    if (ErrorFromChildPackage.Length > 0)
    {
        // Then raise the error that was created in the child package
        Dts.Events.FireError(0, "Capture Error From Child Package Failure",
                ErrorFromChildPackage   
                , String.Empty, 0);

    } // end if the error length of variable is > 0

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

One way is to have the child package populate a variable with the error message, and then read that variable in the parent's OnError (or OnPostExecute) handler.

Upvotes: 0

Related Questions