I am trying to modify my SSIS package to accept both CRLF and LF row terminated files. I have come across the suggestion to use a derived column where the you REPLACE(ColumnName,"\r","") like to so to remove the CR leaving just the LF at the end which should allow both CRLF and LF files to submitted and load correctly.

However what ends up happening is I am getting a blank insted of a NULL value where the field is blank. The table is specifying the field should be NULL and therefore I can only assume it is an issue with this approach. Looking for ideas to get this approach to work and output NULL as there is no data in this field.

Fix Inconsistent Line Terminators in SSIS