Unable to remove returns and line feeds with SSIS

  • I am trying to import several pipe delimited text files.  The files have a field called COMMENT, which sometimes contains a return.  I have viewed the files in Notepad++, and turned on the option to show all characters.  Each normal row ends with a line feed (LF).  The bad comment field sometimes contains just a line feed, and sometimes a carriage return + line feed (CRLF).  

    In SSIS I have a derived column transformation.  I have tried several different expressions to remove the returns.  But nothing has worked for me.  When I run the package and query the table, I still see instances where a row has been shifted down due to the bad comment field.  Here are some of the expressions I have tried.  I have actually tried several other variations of this expression.

    REPLACE(REPLACE(COMMENT,"\x000D"," "),"\x000A"," ")
    REPLACE(REPLACE(COMMENT,"\n"," "),"\r"," ")

    I found these expressions from Googling, and reading other blogs / forums.  But this is not working in my case.  I hope someone can suggest a solution.

    thanks

  • I blogged about something similar a few years ago. Might this help? https://www.timmitchell.net/post/2015/03/23/fix-inconsistent-line-terminators-in-ssis/

  • Hi Tim,

    I am having an issue where there the line terminator is LF, but the flat file contains some CRLF. I tried the method in your blog and it did not resolve the issue. Below is what the flat file looks like in notepad++ (sensitive data blurred for security).  The LF is what should be the line terminator. I've tried the replacing this in a derived column  and in a script component (.replace("\r\n","") and it is not working. Do you have any suggestions?

     

  • @brianksouth I have this exact issue now. Did you get this resolved and do you recall how?

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply