Date: 04/27/06 (SQL Server)    Keywords: no keywords

I have txt file that contains columns that are fixed length and i know each one of the lengths.
The problem is that on the last field in the row it reads in a chunk of the first field on the next line for whatever reason. Row delimeters are the usual {CR}{LF}

I suspect two things:

1. I am not doing some obvious in DTS that I should be.
2. The last field is not padded with spaces that it should be padded with. Txt file comes down from the mainframe so how can I check to see if the file is really padded with the spaces.

UPDATE:I just found out that it is not padded with spaces all the way to 50 characters in the last field of the row. At the end of each line I find a '$' when I used VIM and ':set invlist' to show the characters. There is no option for '$' for line feed in DTS. WTF?! I tried setting it to LF with the same result as just CR and CRLF

SOLVED: Apparently '$' somehow translates into {CR}{LF} in vim so the dollar wasn't the problem. What you have to use instead of source txt file is a bulk insert with a file format where the last row terminator is set to "\r\n". Sweet.

Source: http://community.livejournal.com/sqlserver/47898.html