data conversion SSIS
Hi,
I have data like this ' 8.72-', I am trying to convert it into Numeric and map to a numeric filed , but it is throwing me a error.
I am converting data in to numeric using Derived column.
Please help me
July 13th, 2012 10:04am
I think the minus at the end or the leading spaces ot the decimal sign might be the problem.
try to add a trim: (DT_NUMERIC,10,2)TRIM([your column])
or try (DT_NUMERIC,10,2)TRIM(REPLACE([your column],"-","")) -just to check if that is the problem...
or try (DT_NUMERIC,10,2)TRIM(REPLACE(REPLACE([your column],"-",""),".",",")) -if your local setting is , instead of .
Check your region settings in the Control Panel
(ps next time post the error)
Please mark the post as answered if it answers your question | My SSIS Blog:
http://microsoft-ssis.blogspot.com |
Twitter
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 10:11am
Use the following SSIS Expression in the Derived Column Transformation
(DT_NUMERIC,X,Y)(REPLACE(REPLACE(Column1," ",""), "-",""))
Where X, Y is the needed precision and scale.Arthur My Blog
July 13th, 2012 10:14am
'
8.72-' , if the number is like this , i need to convert this into '-8.720000', replacing '-", infront
Please help me on this
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 10:40am
Then you need to sample for the presence of the '-' and simply appends it, let's try:
FINDSTRING( @[User::Column1],"-",1) > 0? "-" + (REPLACE(REPLACE(@[User::Column1]," ",""), "-","")): (REPLACE(REPLACE(@[User::Column1]," ",""), "-",""))
Add the (DT_NUMERIC,X,Y) in frontArthur My Blog
July 13th, 2012 10:46am
use the expression from ArthurZ. Alternative for replacing the space by nothing is a TRIM.Please mark the post as answered if it answers your question | My SSIS Blog:
http://microsoft-ssis.blogspot.com |
Twitter
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 12:07pm