Value 0000 Considered As NULL Values In The Last Column

Hello

I use a Flat File Connection Manager for a file with 18 columns.

My column delimiter is the "~" caracter and my row delimiter is "{CR}{LF}"

The source files contains about 2300 lines. None of them contain NULL values.

My last row is a numeric(16,2). Even if it is not the appropriate type for the value I want extract, it works with all my columns.

My problem is with the last column. I have read the SQL Server 2005 interpretation of the row delimiter as actually the last row column delimiter.

But, here, my values are OK and put in the destination table if it is not 0 : "+0000000001352" for example in the file.

If it is 0 : "+00000000000000", and if I considered errors as INGORE FAILURE and put data in the destination table, accepting NULL values, I have NULL values in table for these 0 values. And correct values for non-0 values.

How do you explain that ? How I can fix the problem and correctly read my file ?



For your information, if I REDIRECT ROW in the Flat File Error Output, the ErrorCode is : -1071607676 and ErrorDescription is "The data value cannot be converted for reasons other than sign mismatch or data overflow."