Wednesday, January 21, 2009

The value violated the integrity constraints for the column

I was trying to import some data from an Excel spreadsheet into SQL Server today and kept getting this error message.

Error 0xc020901c: Data Flow Task: There was an error with input column "PHNUMBR1" (207) on input "Destination Input" (96). The column status returned was: "The value violated the integrity constraints for the column.".
(SQL Server Import and Export Wizard)

I was having trouble working out what SQL Server was trying to tell me. The table didn’t have any constraints that I could see so I was at a loss. The only seemingly related post was this one, unfortunately in Chinese. But it gave me a clue. I saw the words ‘NOT NULL’ and realised that was the problem. The table had some columns that did not allow NULL entries. I removed these restrictions and the import worked.

So the questions for today are - am I just stupid? Should SQL Server provide a better error message? Should it just populate NOT NULL columns with empty strings?

31 comments:

Anonymous said...

yes, i got same error too. it is a bug. 55555

lckeyral said...

thank, this solve my problems, SQL is soooo stupid

Unknown said...

Thanks for the post...I might have an enjoyable Firday evening yet!

Michael said...

Thanks dude, I had the exact same problem, allowing nulls worked :)

Unknown said...

Bingo. Thanks!

Adam Wilbur said...

Thanks for the info. Saved me a headache...

Anonymous said...

Helped me 2! Thanks!

Matt said...

Thanks man, your article saved me a lot of time.

Anonymous said...

Thanks man. You post solved my problem.

Anonymous said...

Thanks, this post helped me resolved my problem as well.

Microsoft should ensure that the errors returned by SQL Server are more meaningful.

Anonymous said...

Thanks! You nailed it!

Joel Dahlin said...

Perfect, thank you!

Leonmax said...

You are a genius! guessing the answer from a paragraph in foreign language:) and thanks for your link, I got to read the chinese version.

Anonymous said...

awesome!

Anonymous said...

Thanks so much for the solution and the link! I agree with Leonmax, you're really a genius and you did guess it right. I happen to be able to read Chinese and yes, that's the solution they provided. Of course you can remove the null in SQL after the import, as mentioned in the other article.

Anonymous said...

Saved much pain, so obvious once you know. Definitely needs a better error message.

Shibby said...

Thank you! this solved it for me

ML said...

finally i get the solution! thanks a lot!! and yes SQL is so stupid!

Anonymous said...

I got the same error while importing it from Excel

I copied that data into text file and i did import it worked
.

Now I am in a confusion, How come it is behaving so wieredly......

JP

Anonymous said...

this is due to "empty" cells in excel that are not really empty. If you Ctrl+End you will notice that the highlighted box is likely several rows beyond your last row of data. The work around I use is to copy only the data to a new sheet or complete worksheet and save that new file as the "original". The problem is not SQL it is Excel not allowing you to delete cells that no longer contain data and then identifying them as having content to SSIS

Anonymous said...

Actually you don't even need to copy the data to a new sheet. I go to the end of the rows and simply highlight the last 20 rows and right click delete. That re-adjusts the eof point on the file.

You can always open the excel file up as xml and remove the "null" lines.

Anonymous said...

Thanks for the post. Solved my issue.

Itian said...

4 years and this post is still helping software engineers like me. Thanks a lot!

Unknown said...

Awesome, that is exactly the issue I ran into. I checked the columns in my destination table and they were 'not nulls'. Thank you!

Unknown said...

Sweet, that's exactly what my problem turned out to be! 'Not Nulls' in my destination table. Thanks for your help!

Anonymous said...

Thanks Man

Anonymous said...

Works fine for me, really 'NOT NULL' breaks importation (and all imported fields are filled). Thanks!

Unknown said...

I actually got ths when importing data in from .XLSX the document had a blank row on the end of the data, on impoert I got this error, I didn't want to change the design of my table to allow nulls, so I had to fiddle with the sheet to work out my problem but this definitely got me there that little bit quicker...

Anonymous said...

Hi Doogal,
Your post enabled me to find out why my scheduled job would not synch. I did have a column that was not nullable. As the dataset was coming from a database I looked in the database the values were coming from and found the problem there. A primary key without any data in the required field, not sure how the values got in there but I had two entries.
Thank you. Cheryle

Arulraj said...

Suppose you don't want trun out to 'Not Null' in table then you can add ISNULL(COLUMN) in SSIS expression.
Ex: ISNULL(GP) ? 1 : (GP == 1 && amt == 0 ? 0 : 1)
(even if it is meaning less ;))

Unknown said...

Hello Experts,
As said in earlier comments that we should allow NULL values. But what if it is mandatory to hold a value in the field?

Error:
The OLEDB provider “MSDASQL” for linked server “” could not INSERT INTO table “[MSDASQL]”. The Value Violated the Integrity Constraints for one or more Column