last field null chk fails

[madhukar]

A transformer in the job should reject NULL records for a tab delimited sequential file with the following format:

Col1 Col2 Col3
c1val1 c2val1 c3txtval1
c1val2 c2val2 c3txtval2
c1val3 c2val3 c4txtval3
c1val4 c2val4 \n


We have a problem when rejecting Values from Col3. When the first character of the Col3 value is a newline character, the value record doesnt reject out. I have represented the above with the 4th row with \n as the newline character. This is a problem only with the last column value.

The constraints used in the transformer are IsNull(c4txtval3) and Len(Trim(c4txtval3)) = 0. Is there something wrong with the constraints being used?

[novneet]

Actually that is an issue as, you need to set some property for the "Final Delimiter", if you leave it to default which is end, then it will treat the "\n" as the final delimeter.
Probably you need to set some other property as the final delimiter and also need to modify the sequential file accordingly.

[madhukar]

output produced is having an extra column Col4 and output produced is as below.

Col1 Col2 Col3 Col4
c1val1 c2val1 c3txtval1\n c4val1\n
c1val2 c2val2 c3txtval2\n c4val2\n
c1val3 c2val3 c4txtval3\n c4val3\n
c1val4 c2val4 \n c4val4\n

this error is coming only for the last column. Column datatype is varchar

[DSguru2B]

Looking at your data, it seems like that the last row is missing the final tab before the null value.
For nullable fields, give a null_value in the extended properties and constraint on that value.

[madhukar]

After a close analysis here what is happening exactly.

input sequential file property
record delimiter = new line character
field delimiter = tab
final delimiter = none (the last row ends with a new line character)

file has 3 columns col1 col2 col3

output produced has 4 columns with column col4 added at the end

output produced had special thick pipe like character at the end of col3, which after seeing through a text editor found to be a new line character.

Actually while writing to the output file the col3 value is appended with a new line character (last column in input file).

any thoughts on this?

[DSguru2B]

Set you final delimiter to end.
Also the thickened pipe that you are seeing is CR, ascii (013). I bet this is a dos file that you ftp'd over in binary mode. Ftp it again in ascii mode so that the CRLF gets converted to LF (dos to unix) formats. Or find one of the many sed/tr/awk commands to convert dos files to unix.

[madhukar]

100 Marks....!!!!

[DSguru2B]

100 Marks....!!!!

Do I get a golden star too

[ray.wurlod]

There are no marks any more - they converted to Euros some time back.

[DSguru2B]

I think the OP was referring to 100 points rather than currency. Or are you being sarcastic

[ray.wurlod]

Moi?!!