Import conversion error caused by spaces?

I am trying to import a fixed width file where some of the numeric columns are empty. The columns in question are defined as integer columns (of varying sizes) and I am guessing that "empty" columns come across as multiple spaces on the import.

Even though I have "Retain null values from source" checked off, I am still receiving the following error on these empty columns:

Error: 0xC02020A1 at Input Data, Flat File Source [1]: Data conversion failed. The data conversion for column "ToContractExpiryYear" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Input Data, Flat File Source [1]: The "output column "ToContractExpiryYear" (51)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "ToContractExpiryYear" (51)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0202092 at Input Data, Flat File Source [1]: An error occurred while processing file "\\Nastinus-01\ClearingData\OCC\20060320\ser2mst.20060317" on data row 1.

If it is truly the system treating the column as spaces (and not trimming the value), then the only solution I can think off is to source everything as strings, perform a transform that executes a Trim() (Derived Column or Script ?), THEN perform a transform that converts data types, then do whatever else I need...

Am I missing something? Is this the correct solution?

Hi,

On your Flat File Source, try to set the error Output, error column to "Ignore failure".

Then carefully review the result with a data viewer. It appears that you get NULL when there is no value.

Philippe

|||

This solution appears to work well, but is not a realistic solution where you wish to catch true errors (such as alpha data in numeric fields).

One would assume there is an option for fixed width input, to treat blank numeric fields as null. But there are none I can find. Is there any additional information on this subject?

Chris