Oracle SSIS Package Error Response

Question

I'm working on an 2008R2 SSIS package which needs to read a CSV and insert the rows in an oracle database utilising an Oracle Package:

I'm using the 'Oracle Provider for OLE DB' (OraOLEDB.Oracle.1) and the 'OLE DB Command' to communicate with the database.

I can successfully map my parameters to the query and insert rows fine. The issue I'm having is relatied to capturing the error response from oracle should the Oracle package return an error.

When I have my 'OLE DB Command' component set to 'Re-direct Row' the rows piped into my result set, BUT the error description seems to be Generic and does not provide any information related to the actual Error produced at the Oracle DB Package side. I get 'The command execution generated errors.'

See Screenshot:

I get to this description of the error using this code block in a script component and adding an additional output to my re-directed rows:

 public override void Input0_ProcessInputRow(Input0Buffer Row)
{
   Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);


}

I know this is not the actual Error because, when I set the OLE DB Command to Fail the component on error instead of re-directing the row, I can see the actual Error I need to capture on the Package Progress tab.

See Screenshot:

Does anyone know a way to capture these errors, because they appear to have been swallowed by error row re-direction?

Answer 1

How do you want to handle the errors?

1. If you're just interested in logging them in full detail, you can set up logging to a table or file.
2. If you want to do something "interesting" with them but still treat them as an error, you can use an error event handler (multiple events will be raised, one of which contains the detailed message).
3. If neither of these give you exactly what you want, you can always replace the OLE DB Command component with a Script Component where you manually execute the command and catch the resulting exception.

Options 1 and 2 are well-documented; option 3 is more involved and should only be required in advanced scenarios where you like to recover from errors in individual rows in a fully custom manner. Sample:

public override void Input0_ProcessInputRow(Input0Buffer Row) {
    try {
        using (var connection = (OleDbConnection) Connections.Connection.AcquireConnection(null)) {
            using (var command = connection.CreateCommand()) {
                command.CommandText = "TwiddleKnob";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@p0", OleDbType.VarChar, 50).Value = Row.Name;
                command.ExecuteNonQuery();
            }
        }
    } catch (OleDbException e) {
        Row.ErrorDescription = e.Errors.Count > 0 ? e.Errors[0].Message : e.Message;
    }
}

Here the script component must be configured with a connection manager and an output with an "ErrorDescription" column. If a database error occurs, we handle it directly. Although maximally flexible, this has the obvious drawback of requiring you to write manual code for executing statements, which rather defeats the point of using SSIS. Usually either logging or event handling will do what you want.