Knowledge.ToString()

The Value Violated the Integrity Constraints for the Column

SSIS Package Error

You are using SSIS package to load data into SQL table and suddenly you get the error “The value violated the integrity constraints for the column”. It does not give enough information of why the error occurred.

Root Cause for Error

SQL Server has following integrity constraints

  • Primary Key
  • Foreign Key
  • Not Null
  • Unique
  • Check

If you are trying to insert data but any of above mentioned integrity constraints are not satisfied, it will throw an error. For example

  • Value you are trying to insert in primary key is either null or already exists – violates Primary Key constraint
  • Value you are trying to insert in foreign key is either null or does not exists – violates Foreign Key constraint
  • Value you are trying to insert is null but column is defined as Not Null – violates Not Null constraint
  • Value you are trying to insert already exists – violates Unique constraint
  • Value you are trying to insert is not acceptable – violates Check constraint

If Source is Database Table

If the source a database table, make sure that you have correct data for each column.

If Source is Excel Spreadsheet

If the source is Excel spreadsheet, it is a bit tricky to find the root cause. One reason you will get an error is because blank rows exists somewhere in the middle of data or at the very end.

Even though visually you will not be able to verify if the blank rows exists in Excel or not, try to select all the blank rows all the way up to the last Excel row, right click on the selection and press “Delete” to delete it, save the file and rerun the SSIS package again.

Hopefully this would solve your problem.

Share

Comments

5 responses to “The Value Violated the Integrity Constraints for the Column”

  1. luiz fernando Avatar
    luiz fernando

    Muito obrigado!!! muito mesmo. 2020!!!!!

  2. narsing Avatar
    narsing

    Thanks, this solved my issue

  3. Mita Kumari Nayak Avatar
    Mita Kumari Nayak

    When the column will be mandatory , what will write for that column?

    1. Vishal Monpara Avatar
      Vishal Monpara

      Hello Mita,

      If the column is mandatory, most of the time, you need to replicate the business logic at the time of insertion and insert data. If it is not possible, place a dummy/invalid data and update dummy/invalid data in subsequent steps within SSIS. You may want to handle scenario if package fails before dummy/invalid data is updated with valid data. There is also another possibility where you may want to insert the records in a staging table first. You may keep staging table columns optional. Once data is inserted into staging tables and validated, you may want to populate the column with valid data. Then populate real table from staging table data.

      Regards,
      Vishal Monpara

  4. NATRAJ RACHERLA Avatar
    NATRAJ RACHERLA

    there were blank rows in my excel, which resolved the issue.
    Thank You very much!

Leave a Reply

Your email address will not be published. Required fields are marked *