How to Use SSIS Derived Column to Cast a Whole Number with Positive or Negative Symbol to Float?

Question

I have source data in a flat file that is a whole number preceded by either a positive or negative symbol. For example, +0012345 or -0012345, the first value would be translated to 123.45 while the second value would be translated to -123.45.

The destination database field is a float, as I recall this will help to preserve the positive and negative symbols. I've tried several Derived Column transformations including:

(DT_R8) (Substring(FieldName,1,6) + "." + Substring(FieldName,8,2) )

and

(DT_R8) (Left(FieldName,6) + "." + Right(FieldName,2))

Both attempts yield the following error: DER Convert Field Types [2]: An error occurred while attempting to perform a type cast.

The flat file contains a value for each record at this position, except for the trailing record. How do I adjust for the error referenced above and also ensure that the correct value is inserted into my table?

Answer 1

So, my flat file source contains one header row that is skipped by the Connection Manager and also contains a footer row. I feel that the footer row, designated by RecordType = 3, that was the root cause.

I created a Derived Column with the following expression:

RecordType == "2" ? (DT_R8)(LEFT(AdjustmentAmount,6) + "." + RIGHT(AdjustmentAmount,2)) : (DT_R8)"0.00"

Only the rows with RecordType = 2 are evaluated and my original approach successfully parsed the string inclusive of positive / negative symbol into my field.