Wednesday, September 21, 2011

SSIS Error - The value violated the integrity constraints for the column


The error, as you can see below, doesn't say much about what is wrong, it is pretty generic and vague.

[OLE DB Destination [469]] Error: There was an error with input column "ColumnName" (578) on input "OLE DB Destination Input" (482). The column status returned was: "The value violated the integrity constraints for the column.".

The reason (most likely) the SSIS package failed is because(at least) one of the columns in your data source has NULL values and the corresponding column(s) in the destination is set to not allow NULL values. You can fix this by either modifying the destination table to allow NULL values or by setting up a default value for the source column that has NULL values.

Another reason for this error could be that the SSIS package is inserting duplicate values in a column on which a primary key constraint is set up (a primary key column doesn't allow duplicate values and doesn't allow NULLs).

Hope this helps.

12 comments:

  1. Wow, thank you! This was extremely helpful.

    ReplyDelete
  2. Thanks, it helped me out : there were empty rows in my excel sheet.

    ReplyDelete
  3. Thank you for this post !!It was helpful :)

    ReplyDelete
  4. Thank you! I have been wondering this error for 3 hours.

    ReplyDelete
  5. Thank You! I searched for awhile and this was very helpful.

    ReplyDelete
  6. This doesn't fit for me. Hmmm, what other reasons would this error get triggered?

    ReplyDelete
  7. Can you check if you are trying to insert duplicate value into a primary key column? on SSIS Error - The value violated the integrity constraints for the column

    ReplyDelete
  8. This just solves a problem of over 48 hours!
    Thanks

    ReplyDelete
  9. In OLEDB Destination Editor select 'Table or view' in data access mode instead of 'Table or view- fast load'

    ReplyDelete
  10. Thanks for your Post really Handy.

    ReplyDelete
  11. sweet. thanks Faisal.

    ReplyDelete
  12. I have a similar error. To try and correct I filtered the look up request through a conditional split so that all values that are null do not get passed on to the OLE DB Destination. I have confirmed that all the data types are the same, DT_STR and the length is sufficient to handle the string size.

    ReplyDelete