SSIS OLE DB Command error with NOT NULL constrained columns

Symptom:

OLE DB Command fails with the following error reported to the package log:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".

Possible Cause:

The data flow task is experiencing a constraint violation.

For a table ThisTable defined as follows:

ThisTableKey int not null
ThisTableValue varchar(10)

and OLE DB Command data flow task containing the following statement:

update ThisTable set ThisTableValue = 'UpdateVal' where ThisTableKey = ?

where ? has been mapped to a data flow column.

If the data flow column contains a NULL value then the task will fail because of the NOT NULL constraint on the column.

Diagnosis:

This is hard to diagnose in SSMS because the SQL statement above will run fine (and do nothing) when ? is substituted with NULL.

Instead add data viewers to the outputs of the offending OLE DB Command. You should see the following columns at debug time:

• ErrorCode: -1071607702
• ErrorColumn: 12345 (this is an example value)
• ErrorDescription: The data value violated the schema constraint.

Open the package in a text editor and search for 12345. You will find a LineageId recorded against it. Search for the LineageId and you will find the column name (should be ThisTableKey).

Resolution:

Use a Conditional Split task to filter out all NULL values in the data flow column before the OLE DB Command.