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/
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices -
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?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply