SQL server Integration services: How to solve “The value violated the integrity constraints for the column” error?

Standard

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.

Advertisements

3 thoughts on “SQL server Integration services: How to solve “The value violated the integrity constraints for the column” error?

  1. vijay

    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

    Like

    • 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.

      Like

Thank this author by sharing the article on social media. If you have any questions or comments, please leave a reply below:

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s