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:
yes, i got same error too. it is a bug. 55555
thank, this solve my problems, SQL is soooo stupid
Thanks for the post...I might have an enjoyable Firday evening yet!
Thanks dude, I had the exact same problem, allowing nulls worked :)
Bingo. Thanks!
Thanks for the info. Saved me a headache...
Helped me 2! Thanks!
Thanks man, your article saved me a lot of time.
Thanks man. You post solved my problem.
Thanks, this post helped me resolved my problem as well.
Microsoft should ensure that the errors returned by SQL Server are more meaningful.
Thanks! You nailed it!
Perfect, thank you!
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.
awesome!
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.
Saved much pain, so obvious once you know. Definitely needs a better error message.
Thank you! this solved it for me
finally i get the solution! thanks a lot!! and yes SQL is so stupid!
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
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
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.
Thanks for the post. Solved my issue.
4 years and this post is still helping software engineers like me. Thanks a lot!
Awesome, that is exactly the issue I ran into. I checked the columns in my destination table and they were 'not nulls'. Thank you!
Sweet, that's exactly what my problem turned out to be! 'Not Nulls' in my destination table. Thanks for your help!
Thanks Man
Works fine for me, really 'NOT NULL' breaks importation (and all imported fields are filled). Thanks!
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...
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
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 ;))
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
Post a Comment