problem:
you are working on an SSIS package to load a table from a source system and you get an error “The value violated the integrity constraints for the column error” – how do you solve it?
solution:
one the things that the error message should also tell you would be column name. What you want to do is check the table definition of the destination table for any integrity constraints like NOT NULL or PRIMARY KEY. Now once you have that information, go back to your source and figure out if it’s trying to add NULL values in a column that has NOT NULL integrity constraint. Or may be ETL logic is trying to insert duplicate value to the column that has primary key constraint.
Also, the don’t alter the destination table to accept NULL’s or remove integrity constraint. You want to put a logic in your ETL OR fix the data integrity at source. You can use TSQL functions like NULLIF to handle NULL values while querying source systems.
Conclusion:
In this post, we saw how to solve the “The value violated the integrity constraints for the column” error in SSIS.
I have exactly the same error.
Here there are 3 columns with NOT NULL – orderid,orderdetailsid,userid on Destination table.
[OLE DB Destination [345]] Error: There was an error with input column “UserID” (403) on input “OLE DB Destination Input” (358). The column status returned was: “The value violated the integrity constraints for the column.”.
I’m pulling the data from extract source to staging.
Appreciate if you provide me the solution
couple of things to check 1) Are there any null values on user id coming in from source data? Try a derived column task and use the REPLACENULL function to see if that works 2) Do you have User ID defined as primary key – in that case, make sure that the duplicates are not inserted into destination.
Thanks Paras, I found this very helpful
it helped me. I made changes in my ETL. Added where col1 IS NOT NULL.
Please post more ETL error solutions.
Thanks