ssis retain null values from source replaces blank string with null which results into integrity violation

Question

i am using flat files to transfer data from one db to another db using SSIS. to keep the null values as null in destination db. i selected "Retain null" of flatfilesource. but it also replaces my blank strings with nulls. which results into failure for the notnull columns.

any idea how to resolve this?

Answer 1

It can't tell whether it's null or blank string in a flat file because they are actually the same in a flat file. You have to add a derived column after your flat file source to manually manipulate the columns, set the null to empty string for those needed to be.