SSIS OLE DB Destination - Error Output Redirection Error with no Description

Question

I am experiencing something weird. I have a Data Flow Task within an SSIS Package with an OLE DB Source which fetches Data from a table - then there is a Lookup transformation which checks if the Row Key already exists at Destination Table (Primary Key in Destination Table) then the Lookup No match Output is connected to OLE DB Destination. I have also connected the Error Output Of OLE DB destination at a script Component which gets the Error Message and the Error Column in case something goes wrong and Inserts those Data in an Exception Table.

The problem that i face is that i have duplicate Row keys in my Source Table so in that Case the error gets Redirected from OLE DB Destination to my Exception Table but the Description of the Error that i get in Exception Table is "No status is available" instead of "Violation of PRIMARY KEY constraint..Cannot insert duplicate key in object..The duplicate key value is.." Here is a screenshot of the package and here is the script code:

public bool fireAgain = true;
public string customErrorDescription;
public string customErrorDescription;
public string ErrorDescription;
public string ErrorColumnDescription;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
try
{
    var component130 = ComponentMetaData as IDTSComponentMetaData130;

    if (component130 != null)
    {
        Row.DateTimeInserted = DateTime.Now;

        if (Row.ErrorColumn <= 0)
        {
            ErrorColumnDescription = "No Data available - Unknown Error";
        }
        else if (Row.ErrorColumn > 0)
        {

            ErrorColumnDescription = component130.GetIdentificationStringByID(Row.ErrorColumn);
        }
        ErrorDescription = component130.GetErrorDescription(Row.ErrorCode).Replace("\r\n", " ");

        //Writes the error as Information in Logging
        customErrorDescription =
         $"Error in OLE DB Destination:{ErrorDescription}" +
         $"In Column: {ErrorColumnDescription}";
        ComponentMetaData.FireInformation(10, "Error", "Error", ErrorDescription, 0, fireAgain);

        Row.ErrorDescription = ErrorDescription;
        Row.ErrorColumnDescription = ErrorColumnDescription;
        Row.DestinationTableName = Variables.DestinationTable;
        Row.SourceName = "Source_View";
        Row.SourceType = "View";
    }
}
catch (Exception Ex)
{
    ComponentMetaData.FireError(10, "Error", Ex.Message, Ex.Message, 0, out fireAgain);
}

}

I would like to mention that in other errors like truncation or null insert in a column that null is not allowed, this seems to work fine.

For example In truncation, I get: "Conversion failed because the data value overflowed the type used by the provider." In the NULL constraint violation, I get: "The data value violates integrity constraints."

Any Suggestions or Thoughts on why is this happening only with Dublicate key insert? Thanks in advance!

Answer 1

You should note that two types of validations are performed y OLE DB Destination: (1) Client-side and (2) Server-side. You can check the following answer for more details:

• Fast load error output doesn't redirect entire batch

The SSIS engine does not catch the descriptions of the server-side errors (foreign keys, triggers...) at runtime while the client-side validation errors' descriptions (truncation, Not null...) are readable.