====== Row diversion on error ======

SSIS easily supports the diversion of error rows, but diagnosis of the error that caused the row to be diverted is less straightforward. A data flow component's error output includes all of its input columns, with two additional ones:
  * ''ErrorCode'' -- the SSIS code identifying the type of error encountered;
  * ''ErrorColumn'' -- the integer ID of the column that caused the error.
Neither of these two attributes are immediately human-readable. This article presents a pattern for translating them using a script component that requires only minimal modification for inclusion in a new data flow.

===== Using the pattern =====

  - Build your data flow as normal. 
  - Drag a script component onto the data flow surface, setting its type to 'Transformation'
  - Take the error output of the component requiring diversion handling and connect it to the input of the new script transformation. Change the row error handling mode from 'Fail component' to 'Redirect row'
  - Double-click on the script component to open its editor, then revise it as follows:
    * On the **Input Columns** page, select **all** available input columns
    * On the **Inputs and Outputs** page, drill down into the properties of the components output ('Output 0') and click on 'Output Columns'
    ** Use the 'Add column' button to add columns with the following Name and DataType properties:
      * ''ErrorDescription'' DT_WSTR(255) -- this column will contain the description associated with the ''ErrorCode'' value supplied by SSIS
      * ''ErrorColumnName'' DT_WSTR(128) -- this column will contain the column name associated with the ID supplied by SSIS in ''ErrorColumn''
      * ''ErrorValue'' DT_NTEXT -- this column will contain the value of the column that caused the error 
      * ''RowId'' DT_WSTR(255) -- this column will contain an (implementation-specific) row identifier, identifying the row that caused the error
    .. The latter two columns may be unnecessary if you are diverting errors into destination-specific error tables, but will be useful if you are diverting them into a single generic error table.
    * On the **Script** page, click 'Edit Script'.
  - The default script that opens in VSTA includes the line ''public class ScriptMain : UserComponent''. This is the declaration of the ''ScriptMain'' class. 
    * Select the code for this pattern ([[#the pattern|below]] -- you can select the whole block by double-clicking on it somewhere) and copy it to the clipboard
    * Paste it into the script, replacing the existing class declaration and **everything** after it in the script file.
    ** In the body of method ''Input0_ProcessInputRow'', edit this line:
    .. ''Row.RowId = Row.YourRowIdFieldNameHere_IsNull ? "" : Row.YourRowIdFieldNameHere.ToString();''
    .. replacing ''YourRowIdFieldNameHere'' and ''YourRowIdFieldNameHere_IsNull'' with the properties representing your own unique row identifier. (It doesn't actually matter //what// property you specify here -- but it's useful to have a row ID. If you don't have one in your data flow, it may be useful to [[adding row numbers to SSIS dataflows|add a row number in the data flow itself]].)
  - Save, close, etc. Now you can connect the output of the script component to write the translated error details into a destination of your choice (flat file, error table, etc).
 

===== The pattern =====
For each row that passes through the component, the ''Input0_ProcessInputRow'' method populates the four new columns:
  * ''RowId'' is given the value of the property you specify when you replace ''YourRowIdFieldNameHere'' and ''YourRowIdFieldNameHere_IsNull''
  * ''ErrorDescription'' is looked up in the component's metadata
  * ''ErrorColumnName'' is determined by looking up the column's identification string from its ID, then transforming it is something close to the original column name by comparing it to the list of input columns. (The list of input columns is cached in the ''columnNames'' list during the execution of the component's ''PreExecute()'' method).
  * A type-independent ''ErrorValue'' is read from the errored column as a byte array. This enables the script to remain metadata-agnostic and much more reusable.

<code csharp>public class ScriptMain : UserComponent
{
  private System.Collections.Generic.List<string> columnNames;
  private IDTSComponentMetaData130 metadata;

  public override void PreExecute()
  {
    base.PreExecute();
    metadata = this.ComponentMetaData as IDTSComponentMetaData130;
    columnNames = new System.Collections.Generic.List<string>();
    foreach (IDTSInputColumn130 c in metadata.InputCollection[0].InputColumnCollection)
      columnNames.Add(c.Name);
  }

  public override void Input0_ProcessInputRow(Input0Buffer Row)
  {
    try
    {
      var columnName = GetColumnName(Row.ErrorColumn);

      Row.RowId = Row.YourRowIdFieldNameHere_IsNull ? "" : Row.YourRowIdFieldNameHere.ToString();
      Row.ErrorDescription = metadata.GetErrorDescription(Row.ErrorCode);
      Row.ErrorColumnName = columnName;
      Row.ErrorValue.AddBlobData(GetColumnValue(columnName, Row));
    }
    catch (Exception e)
    {
      // transform .Net error into SSIS error
      bool pbCancel = true;
      metadata.FireError(-1, "", e.StackTrace, "", -1, out pbCancel);
    }
  }

  private string GetColumnName(int errorColumn)
  {
    if (errorColumn < 1)
      return "<unavailable>";
    string idStr = metadata.GetIdentificationStringByID(errorColumn);
    foreach (string c in columnNames)
      if (idStr.EndsWith("[" + c + "]"))
        return c;
    return idStr; // if column names have been revised between the errored component and this one...
  }

  private byte[] GetColumnValue(string columnName, Input0Buffer row)
  {
    columnName = columnName.Replace("_", "");

    object value = null;
    foreach (var p in row.GetType().GetProperties())
      if (p.Name == columnName + "_IsNull")
        value = (bool)p.GetValue(row) ? "<null>" : row.GetType().GetProperty(columnName).GetValue(row);
    string str = value == null ? "<unavailable>" : value.ToString();

    byte[] bytes = new byte[str.Length * sizeof(char)];
    System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
    return bytes;
  }
}</code>


{{tag> ssis csharp}}