I have a very simple text file that contains two comma separated values that is about 100 lines long. This file is created by an automated process (that I cannot control) and I import this file into SQL via SSIS.
My job works very well except when there is a blank line within the file. By this, I mean it is completely blank - no commas or other characters. When this exists in the file, the record directly after it will be imported with two spaces before the imported value.
For example, if the text line contains this "ABC,123", the imported SQL value will be " ABC" for the first column. I have tried to remove this by using a derived column with the TRIM statement, but that had no effect. The REPLACE function also did not work. The really strange part is that if I add a Data Viewer directly before the data flow Destination, the value looks fine. I even added asterisks so that I could "see" the spaces if they exist, like this:
"*" + REPLACE([Column 0]," ","") + "*"
This is an extremely annoying issue, and I would greatly appreciate any suggestions. Thank you!
Got it - someone else replied to this in another forum. I needed to remove the invisible CRLF characters that were coming across like this:
Just FYI in case someone else stumbles across this...