# 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.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.