SSIS Flat File with Numeric DataType with Spaces

Question

I'm using SSIS to load a fixed length Flat File into SQL.

I have a weight field that has been giving me trouble all day. It has a length of 8 with 6 DECIMAL POSITIONS IMPLIED (99V990099).

The problem i'm having is when it isn't populated and has 8 spaces.

Everything i try gets an error:

"Invalid character value for cast specification"." OR "Conversion failed because the data value overflowed the specified type.". OR Data conversion failed. The data conversion for column "REL_WEIGHT" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I've tried declaring it as DT_String & DT_Numeric.

I've tried many variations of:

TRIM([REL_WEIGHT])=="" ? (DT_STR,8,1252)NULL(DT_STR,8,1252) : REL_WEIGHT

ISNULL([REL_WEIGHT]) || TRIM([REL_WEIGHT]) == "" ? (DT_NUMERIC,8,6)0 : (DT_NUMERIC,8,6)[REL_WEIGHT]

TRIM(REL_WEIGHT) == "" ? (DT_NUMERIC,8,6)0 : (DT_NUMERIC,8,6)REL_WEIGHT

But nothing seems to work.

Please someone out there have the fix for this!

Answer 1

I think you may be running afoul of the following point, explained nicely at http://vsteamsystemcentral.com/cs21/blogs/applied_business_intelligence/archive/2009/02/01/ssis-expression-language-and-the-derived-column-transformation.aspx:

You can add a DT_STR Cast statement to the expression for the MiddleName, but it doesn't change the Data Type. Why can't we change the data type for existing columns in the Derived Column transformation? We're replacing the values, not changing the data type. Is it impossible to change the data type of an existing column in the Derived Column? Let's put it this way: It is not possible to convert the data type of a column when you are merely replacing the value. You can, however, accomplish the same goal by creating a new column in the Data Flow.

I've solved this on past occasions by loading the data from the flat file as strings, and then deriving a new column in a Derived Column transformation which is of numeric type. You can then perform the appropriate trimming, validation, casting, etc. in the SSIS expression for that new column in the transformation.

Here, I found an example SSIS expression I used at one point to derive a time value from a 4-digit string:

(ISNULL(Last_Update_Time__orig) || TRIM(Last_Update_Time__orig) == "") ? NULL(DT_DBTIME2,0) : (DT_DBTIME2,0)(SUBSTRING(TRIM(Last_Update_Time__orig),1,2)+":"+SUBSTRING(TRIM(Last_Update_Time__orig),3,2)+":00")

Answer 2

There has to be a better way to do it, But i found a way that works.

Create a Derived Column Expression: TRIM(REL_WEIGHT) == "" ? (DT_STR,9,1252)"0.0000000" : (DT_STR,9,1252)(LEFT(REL_WEIGHT,2) + "." + RIGHT(REL_WEIGHT,6))

THEN Create a Data Conversion Task to change it to Numeric and set scale to 6.

And then Map the [Copy of NewField] to my SQL table field set up as Decimal(8,6).

I don't know how the performance will be of that when loading a million records, probably not the best. If someone knows how to do this in a better way performance wise please let me know.

Thanks, Jeff