3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1949225
  • 33
  • 1
  • 1
  • 3

2 Answers2

2

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.

bobs
  • 21,844
  • 12
  • 67
  • 78
1

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]

enter image description here

Justin
  • 9,634
  • 6
  • 35
  • 47
  • Thanks for the information. Can you explain why my approach above will not work? Based upon what I'm seeing, it did appear to work. – user1949225 Jan 04 '13 at 20:46
  • For example (sorry, can't post images): Derived Column Name Derived Column Expression Data Type ------------------- -------------- ---------- --------- Final_Price [sign]+[price] decimal[DT_DECI So: Price = 232.43 Sign= - becomes -232.43 and when I load into my database this value goes into the column on the table defined as decimal(10,2) as -232.43. Just want to make sure I understand the benefits of the second approach over the first. Thanks again. – user1949225 Jan 04 '13 at 21:11
  • I see, but still don't know why this works, because `Sign` data type must be string, then you want sum `Sign` string and `Price` decimal this should not work. http://sqlfiddle.com/#!3/d41d8/7527 What data types are before derived columns of `Sign` and `Price`. – Justin Jan 04 '13 at 21:28