Converting a string to a decimal value in an SSIS package

Question

I have a flat file that I am importing into SQL Server 2008 using an SSIS package.

The file contains a field with a number, followed by a column with a negative sign when the preceding column value is negative.

For instance:

Price    Sign
-----    -----
9212.00 
  29.01    -
 268.00    -
 453.02

I want to load this value into a decimal column on a table.

In order to accomplish this, I am creating a derived column in my Data Flow Task. The expression for the derived column is [Sign] + [Price]. I have (using the Advanced Editor) defined the derived column as type decimal [DT_DECIMAL].

Then I'm simply loading the data (along with the derived column) into my table.

Does this sound like a good way to handle this situation? Is there another method that I should consider instead? Any 'gotchas' with using this method?

Thanks.

Solution 1

Your approach seems to be the best option. The default data type for each column from the flat file is string. So, your [Sign] + [Price] expression is doing string concatenation. And, the data is implicitly converted to a decimal value when moved to your output column.

One change that would improve readability is to explicitly cast the string value to a decimal. You can change your expression in the Derived Column component to this:

(DT_DECIMAL, scale)[Sign] + [Price]

where scale is a number that will match the data type for your output column.

With this change, the output from your Derived Column component will be the decimal data type.

Solution 2

Maybe this will not work...

1. You need to convert Sign to string data type and Price to numeric.
2. Then compare if Sign is "-", if is, multiple from -1.

Derived Column Code Example:

(DT_WSTR, 1) [Sign]=="-"?  [Price]*-1 :  [Price]