Field Terminator Problem in Format Files
After a whole day struggling with Line Feed/Hex code, I finally found that my problem was caused by a missing columns in the format files. The dataset is new to me and I thought it had 21 rows while it actually has 22 rows. That's why sql server kept loading the last "|" from the data file.

Here is a reference for others maybe interested in loading data file from UNIX system with row terminators different from Windows, there are three methods:


use bulk insert and set the row terminator as "0x0a".
use a format file and set the the field terminator of the last field as "\n".
Use a dynamic sql with function Char(10) to insert the line feed(LF) into the command string and then exec() the command string.


Before trying these methods, use a notepad to show all control characters to confirm the control characters used by the text files.

If none of these methods work for you, you may have some problems other than the line feed/row terminator.