SSIS Conditional Split - force Default path to thr

问题:

I am reading data from a source in the Data Flow tab.

The data is put through a Conditional Split

Order 1 -> ColA = "Y"

Order 2 -> ColB = "Y"

If the Default output condition gets hit, being neither ColA or ColB = "Y"... then I want to throw an exception / error so that it hits the "Fail Component" block.

回答1:

Pick your poison and connect it to the Default output.

The quick and dirty is to attach a Derived Column Transformation and force a division by zero error.

My preference is to route to a Script component. In the script component, I pass in the business keys of the rows as ReadOnly. For each row I see, I raise a Warning event so I have record of all the "bad" rows. In the PostExecute method, if any rows had been sent the component, I then raise the OnError event to force the Data Flow to report an error back to the control flow. This will satisfy your need for a control to switch to the "Fail Component block" and you will be armed with sufficient information in your logs to research the skunky data.

You are logging, right? (the answer is yes. If you aren't make it so :D)

Below is code I use when a lookup fails. My business keys are Employee ID and Effective date so I add them to a list in the ProcessInput method and then in the PostExecute I fire off the Warnings/Errors. You could just as easily raise the Warnings in the ProcessInput method and set a flag in there to signal the PostExecute that it needs to error out.

public class ScriptMain : UserComponent
{
    List<KeyValuePair<string, string>> notFound;    

    public override void PreExecute()
    {
        base.PreExecute();
        notFound = new List<KeyValuePair<string, string>>();
    }

    public override void PostExecute()
    {
        base.PostExecute();
        foreach (KeyValuePair<string, string> kvp in notFound)
        {
            string msg = string.Format("Research->{0}:{1}", kvp.Key, kvp.Value);
            ComponentMetaData.FireWarning(0, "Unmatched Employees", msg, string.Empty, 0);
        }

        if (notFound.Count != 0)
        {
            bool cancel = true;
            ComponentMetaData.FireError(0, "SCR Lookup Employee", "Unmatched Employees found. See warning for more context", string.Empty, 0, out cancel);
        }

    }


    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        KeyValuePair<string, string> kvp = new KeyValuePair<string,string>(Row.EmployeeID, string.Format("{0:yyyy-MM-dd}", Row.EffectiveDT));
        notFound.Add(kvp);        
    }

}