The value violated the integrity constraints for the column

Question

I am trying to import the data from Excel file into SQL Server database. I am unable to do so because I am getting following errors in the log file. Please help. The log erros are as as follows:-

[OLE DB Destination [42]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [42]] Error: There was an error with input column "Copy of F2" (5164) on input "OLE DB Destination Input" (55). The column status returned was: "The value violated the integrity constraints for the column.".

[OLE DB Destination [42]] Error: The "input "OLE DB Destination Input" (55)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (55)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (42) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

[Excel Source [174]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "Excel Source" (174) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Yeager

Can you show us your code? – Zoie

Answer 1

It usually happens when Allow Nulls option is unchecked.

Solution:

1. Look at the name of the column for this error/warning.
2. Go to SSMS and find the table
3. Allow Null for that Column
4. Save the table
5. Rerun the SSIS

Try these steps. It worked for me.

See this link

Eton

Answer 2

Delete empty rows from Excel after your last row of data!

Some times empty rows in Excel are still considered as data, therefore trying to import them in a table with one or more non nullable columns violates the constrains of the column.

Solution: select all of the empty rows on your sheet, even those after your last row of data, and click delete rows.

Obviously, if some of your data really does vioalte any of your table's constraints, then just fix your data to match the rules of your database..

Crop

This worked for me, I thought I was going crazy. Thank you! – Bradney

Sure enough this is exactly what helped me to import an Excel 2007-compatible worksheet into a SQL Server 2014 database table; there were rows that seemed empty under my data in the spreadsheet but they were formatted rows. Deleting that took care of it. Thanks! – Marabout

Answer 3

As a slight alternative to @FazianMubasher's answer, instead of allowing NULL for the specified column (which may for many reasons not be possible), you could also add a Conditional Split Task to branch NULL values to an error file, or just to ignore them:

Angulo

I second your opinion :-) – Eton

Answer 4

It's as the error message says "The value violated the integrity constraints for the column" for column "Copy of F2"

Make it so it doesn't violate the value in the target table. What the allowable values are, data types, etc are not provided in your question so we cannot be more specific in answering.

To address the downvote, No, really it's as it says: you are putting something into a column that is not allowed. It could be Faizan points out, that you're putting a NULL into a NOT NULLable column, but it could be a whole host of other things and as the original poster never provided any update, we're left to guess. Was there a foreign key constraint that the insert violated? Maybe there's a check constraint that got blown? Maybe the source column in Excel has a valid date value for Excel that is not valid for the target column's date/time data type.

Thus, baring concrete information, the best possible answer is "don't do the thing that breaks it" In this case, something about "Copy of F2" is bad for the target column. Give us table definitions, supplied values, etc, then you can specific answers.

Telling people to make a NOT NULLable column into a NULLable one might be the right answer. It might also be the most horrific answer known to mankind. If an existing process expects there to always be a value in column "Copy of F2" changing the constraint to NULL can wreak havoc on existing queries. For example

SELECT * FROM ArbitraryTable AS T WHERE T.[Copy of F2] = '';

Currently, that query retrieves everything that was freshly imported because Copy of F2 is a poorly named status indicator. That data needs to get fed into the next system so... bills can get paid. As soon as you make it such that unprocessed rows can have a NULL value, the above query no longer satisfies that. Bills don't get paid, collections repos your building and now you're out of a job, all because you didn't do impact analysis, etc, etc.

Toddy

Answer 5

I've found that this can happen due to a number of various reasons.

In my case when I scroll to the end of the SQL import "Report", under the "Post-execute (Success)" heading it will tell me how many rows were copied and it's usually the next row in sheet which has the issue. Also you can tell which column by the import messages (in your case it was "Copy of F2") so you can generally find out which was the offending cell in Excel.

I've seen this happen for very silly reasons such as the date format in Excel being different than previous rows. For example cell A2 being "05/02/2017" while A3 being "5/2/2017" or even "05-02-2017". It seems the import wants things to be perfectly consistent.

It even happens if the Excel formats are different so if B2 is "512" but an Excel "Number" format and B3 is "512" but an Excel "Text" format then the Cell will cause an error.

I've also had situations where I literally had to delete all the "empty" rows below my data rows in the Excel sheet. Sometimes they appear empty but Excel considers them having "blank" data or something like that so the import tries to import them as well. This usually happens if you've had previous data in your Excel sheet which you've cleared but haven't properly deleted the rows.

And then there's the obvious reasons of trying to import text value into an integer column or insert a NULL into a NOT NULL column as mentioned by the others.

Hautesavoie

Fourth solution worked for me i.e. deleting empty rows below – Ferro

Answer 6

Teradata table or view stores NULL as "?" and SQL considers it as a character or string. This is the main reason for the error "The value violated the integrity constraints for the column." when data is ported from Teradata source to SQL destination. Solution 1: Allow the destination table to hold NULL Solution 2: Convert the '?' character to be stored as some value in the destination table.

Selemas

Answer 7

You can replace the values "null" from the original file & field/column.

Echevarria

Answer 8

the point can be if you are not using valid login for linked server. Problem is on destination server side.

There are few steps to try:

1. Align db user and login on destination server: alter user [DBUSER_of_linkedserverlogin] with login = [linkedserverlogin]

2. recreate login on destination server used by linked server.

3. Backup table and recreate it.

2nd resolved my issue with "The value violated the integrity constraints for the column.".

Catenane

Answer 9

In my case I simply had to specify type of columns in Excel file by selecting each column and right clicking -> Format Cells -> Number -> Category. There I chose for each column the right type it had to be, according to the field type of the table I would insert it to later on, and not leave it as General as this was causing the problem at my OLE DB Destination task in Data Flow of Integration Services. Hope this helps others too.

Electuary