A error,details was encountered today when inserting data into the OLE DB destination Columns[fullname] violation of the integrity constraint.
[OLE DB Destination [12]] Error:there was a error with OLE DB destination.inputs[ole db Destination Input]. Columns[fullname] on OLE DB destination.inputs[ole db Destination Input]. The column status returned is: "Thevalue violated the integrity constraints for the column."
Integrity constraint includes: domain integrity constraints, entity integrity and referential integrity, Columns[fullname] does not have entity integrity constraints (PK) and referential integrity constraints (FK). The only possible violation is the domain integrity constraint. Domain integrity constraints include: CHECK constraint,default Constraint, NOT null Constraint (non-null constraint), Unique Constraint, exclude Check,default,unique, The only integrity Constraint that can be violated is the NOT null Constraint, which is the nullability of the table column.
SSIS does not have any properties for handling SQL Table column nullability, such as Illustration1 and Illustration2, and the wrong root cause is OLE DB Source Component The returned column FullName has a null value, but the column (FullName) of the OLE DB Destination Table defines NOT NULL, which is not allowed to be null.
SSIS nullability that does not process column nullability,table is handled by SQL Server database engine. When SSIS inserts data by calling the BULK INSERT command, SQL Server detects that the data insert operation violates the nullability of the table, stops the transaction from running, and errors the error to SSIS.
Because SSIS does not process column nullability, you only need to modify the nullability of the table column, and you do not need to modify the SSIS package to fix this error.
Properties of the external Columns and Output Columns of the Illustration1,datasource (OLE DB Source) component
Illustration2,datadestination (OLE DB Destination) component properties of the external Columns and input Columns
How SSIS handles the nullability of column