• Status: Solved
• Priority: Medium
• Security: Public
• Views: 340

# Derived columns using ssis 2008

Currently it looks like 0015000, this should be .15000

My expression in the Derived Column Tramsformation editor is

(DT_DECIMAL,7)(TRIM([SUB ACCT RATE]))

Results comes back as 15000 and not .15000

What i'm I doing wrong?

Pulling my hair out, thanks in advance.
0
gilweber3
3 Solutions

Converting 15000 to decimal still gets you 15000.  You need to perform a little math or string manipulation (add a '.' in front of the string after trimming and before converting) to get the results you want.
0

Commented:

(DT_DECIMAL,7)(TRIM([SUB ACCT RATE]))/100000

Will this work all through your table? ie.... is the SUB ACCT RATE field always numbers? Is it always not 0?
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
<Minor point, I'll let you guys come up with the formula>
Numeric data types do not support leading and trailing zeros, so numeric .15000 will equal .15, so if the trailing zeros are important then the expression should be a converstion to a string of Tony303's expression above, insuring that it covers five decimal numbers.
0

Author Commented:
Sorry guys

Currently it looks like 0015000, this should be .0015000

My expression in the Derived Column Tramsformation editor is

(DT_DECIMAL,7)(TRIM([SUB ACCT RATE]/100000))

Results comes back as .15000 and not .0015000
0

Commented:
Divide by 10000000 instead of 100000.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.