DSXchange

000255 000431 C BC
R SKD 1740 OUT 1925 G/T D23A PSV 1925 CGO 1819 R MEL RTG N 390 MEL.'\n'
000125 000139 888'\n'
02000 002014 CO 888'\n'
001435 001508 O K AC CREW REPORTS LH FAIRING TO FUSELAGE RETRACTABLE'\n'

Above is fixed width file except for the last record which can be variable. So have defined them as varchar with null field value as Null.The two records in the middle are being reject with Input buffer overrun at field because the last field is null.

I am not sure how to get around this, can someone please advice.

What you are doing is rght, define the last field as varchar and put the null value as ''. Also do not forget to define the record delimiter (Unix/Dos format) and final delimiter in the format.

I have done those settings but still the 2 records are being rejected.

Is there a \n at the end of the first line?

Yes when I od -c filename , I do see a \n at the end of the first line. That is why I have used a record delimiter as UNIX newline.

Your original data, as posted, lacked this \n, which is why I asked.

Can you read the file as if it has only one VarChar field? Does that work successfully? If so, you can parse the rows in a Transformer stage. (This can actually be more efficient, because the parsing occurs in a parallel stage.) Your parse could be based on the number of space characters in the line - if three, the fifth field is empty/null. If more, the fifth field contains data.

Yes when I read the entire record as a single Varchar column, it works fine. Thank you Ray, I will trying to parse it in the transformer stage and update how it goes...