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   quot Microsoft SQL Native Client quot   Hresult  0x80040E21  Description   quot Multiple-step OLE DB operation generated errors  Check each OLE DB status value  if available  No work was done  quot    OLE DB Destination  42   Error  There was an error with input column  quot Copy of F2 quot   5164  on input  quot OLE DB Destination Input quot   55   The column status returned was   quot The value violated the integrity constraints for the column  quot    OLE DB Destination  42   Error  The  quot input  quot OLE DB Destination Input quot   55  quot  failed because error code 0xC020907D occurred  and the error row disposition on  quot input  quot OLE DB Destination Input quot   55  quot  specifies failure on error  An error occurred on the specified object of the specified component   DTS Pipeline  Error  The ProcessInput method on component  quot OLE DB Destination quot   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  quot WorkThread0 quot  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  quot Excel Source quot   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

User · Answer

It usually happens when Allow Nulls option is unchecked   Solution    Look at the name of the column for this error warning  Go to SSMS and find the table Allow Null for that Column  Save the table Rerun the SSIS   Try these steps  It worked for me   See this link

User · Answer

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

User · Answer

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

User · Answer

You can replace the values  null  from the original file  amp  field column

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   Align db user and login on destination server  alter user  DBUSER of linkedserverlogin  with login    linkedserverlogin   recreate login on destination server used by linked server   Backup table and recreate it    2nd resolved my issue with  quot The value violated the integrity constraints for the column  quot

[ssis] The value violated the integrity constraints for the column

Examples related to ssis