Post reply

how to remove'- ' in the integer value

  • January 6, 2009 at 2:04 pm

    #133566

    i have the column with the values like 453-5654-456.in the derived column i want didplay this one as 4535654456.

  • January 6, 2009 at 3:02 pm

    #922213

    Try

    REPLACE(Col_Name,"-","")

  • January 6, 2009 at 4:29 pm

    #922256

    thanks for your answer.

    But this answer is not working in ssis 2005,when i used it as expression in derived column.So, can you plesase help me is there any other option...

  • January 6, 2009 at 5:36 pm

    #922269

    I tested it in SSIS and it works fine for me. When you say 'Not working' - does it not give you the desired result or does it error?

    If you are using OLE DB Source you can modily the query itself to get rid of '-'

    ~Mukti

  • January 7, 2009 at 9:13 am

    #922645

    Thank you very much for your reply.

    Its working fine when i extract the data from oledb source.

    but my source is flatfile.I am trying to extract the data from flat file, but i m getting error like expression error. So can anybody please help me to solve this problem.

  • January 7, 2009 at 2:44 pm

    #922938

    Check the datatype of the column in flat file connection. Also in the Derived Column Transformation when you get an errro (red expression), try to hover over it and see what the exact error is?

  • January 8, 2009 at 3:36 am

    #923170

    i´m experiencing the same problem with derived columns.

    The Source Column has larg Text and is of Data Type "four-byte signed integer [DT_I4]". Then when i try the RAPLACE like = REPLACE( (DT_I4) "Desciption",";"," - ") in the Derived Column Transformation Editor "Expresion", i get this Error:

    Error at Data Flow Task [Derived Column [2042]]: Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_I4.

    Error at Data Flow Task [Derived Column [2042]]: Casting expression ""Description"" from data type "DT_WSTR" to data type "DT_I4" failed with error code 0xC00470C2.

    Error at Data Flow Task [Derived Column [2042]]: Evaluating function "REPLACE" failed with error code 0xC00470C4.

    Error at Data Flow Task [Derived Column [2042]]: Computing the expression

    "REPLACE( (DT_I4) "Description",";"," - ")" failed with error code 0xC00470C5.

    The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

    Error at Data Flow Task [Derived Column [2042]]: The expression "REPLACE( (DT_I4) "Description",";"," - ")" on "input column ""Leistungsbeschreibung"" (3882)" is not valid.

    Error at Data Flow Task [Derived Column [2042]]: Failed to set property "Expression" on "input

    column ""Description"" (3882)".

    ------------------------------

    ADDITIONAL INFORMATION:

    Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

    How can this be solved?

    Thx.

  • January 8, 2009 at 10:05 am

    #923488

    change the advanced property of flat file is text Qualifier=true

    Try it

  • January 8, 2009 at 12:54 pm

    #923647

    I am bit confused now, in your original question you said -

    i have the column with the values like 453-5654-456.in the derived column i want didplay this one as 4535654456.

    Now 453-5654-456 can't be int type, so i guess you need to change this to string datatype for the flat file connection.

    secondly your replace function REPLACE( (DT_I4) "Desciption",";"," - ") is trying to cast the Description column before the replace function - this will error.

    If you are trying to create an int type result you need to cast it after the Replace function, like:

    (DT_I4)REPLACE([Description],"-","")

  • January 8, 2009 at 4:25 pm

    #923813

    I am not familiar with how this works in SSIS, but I have done a lot of work cleaning up data from text files and other hacked/dubious file source, and sometimes the reason REPLACE([ColumnName],'-','') doesn't work is because the character looks like a '-' but isn't using the standard ascii code.

    See if you can get the actual ascii code value by using SELECT ASCII('-') in an SSMS query, pasting the - from your flat file source. That should return an integer, let's call it x;

    Try REPLACE([ColumnName],CHAR(x),'') and see if that helps. Or if it is a Unicode character and you can figure out the number for it, REPLACE([ColumnName],NCHAR(x),''). I find with text sources, it's safer to use CHAR() or NCHAR() to strip out a character than typing in the string.

    It should only take a few minutes to check, it might be worth a try.

  • January 8, 2009 at 6:20 pm

    #923838

    yes this is working thank you for your answer

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply