We have a SSIS package that imports data from a csv file straight into a staging table. When we run the package i get the error The value violated the integrity constraints for the column the column and date column which sometimes contains a date e.g. 2011-10-31,as a varchar or the text "#N/A notspecified" or it is blank.The destination column definition in the stage table is specified as not null. The provider of the csv uses excel to modify the files. Due to issues with excel and empty spaces at the end of the file we ask the user to put a space at the end of the file for the column if the data is blank.In the package there is also a derived column transformation that has the following expresison:(DT_STR,8,1252)(SUBSTRING(ANNOUNCE_DT,1,4) == "#N/A " ? "" : SUBSTRING(REPLACE(ANNOUNCE_DT,"-",""),1,10))
If all this field are empty and the user puts in a space then the package fails with the above error. However if "A" or any other text is placed in the field the package runs. Can anyone explain why this is happening. It seems that space is treated as a null. I am not sure what I need to do to cater for all possible entries in this column. I chanegd the derived expression to ISNULL(ANNOUNCE_DT)?" " : ANNOUNCE_DT
but it didnt make a difference. Anybody have any ideas?
SSIS Error – The value violated the integrity cons
Posted - 2011-11-28 : 07:07:56
