Converting character value to decimal in SSIS

Question

Hi everybody,

My column is defined as decimal(12,2) in the SQL Server. The value in the text file looks like this:

0011651200

It should be converted to 1165.12 (e.g. there are two '00' added at the end of the amount). I'm using the following expression in SSIS

(DT_NUMERIC,12,2)((DT_NUMERIC,12,2)(CheckAmount) / 10000.00)

When I click F5 and use a data viewer, I can see my data correctly converted (e.g. the amount shows correctly with 2 decimal points). Yet in SQL Server I'm seeing the result * 100, so values come incorrectly.

What should I do to fix the issue?

Thanks in advance.

Answer 1

Looks like my column was pointing to the original column in the mapping, that's probably why I was getting wrong values. With varchar(10) and re-pointing I got the values correctly finally! Will try to set back to decimal and re-try.

Edit. I was killing myself by pointing to the wrong column (original vs new derived). With decimal(12,2) it also works fine now.

Answer 2

We're using a stage table, but our framework is very complex and the conversion is supposed to happen "automagically". I don't see a simple way (within our framework) to add these conversions.

In any case, it was my own mistake. I spent ~20+ min. trying to figure out what's wrong while my original column was pointing to the SQL table column and not my new derived column. With the derived column is works now as expected.