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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics