none
Type conversion in SSIS RRS feed

  • Question

  • Hi All,

    I want to convert a string column to integer column (all numeric values to integer and alpha-numeric vales to null). I am able to perform this with the help of a expression. But the expression also converts "." (dot) to 0 (zero). For my requirement, "." (dot) should be treated as null. Please help me to write an expression.

    Here is the expression I am using:

    (DT_I4)column ==(DT_I4)column ? (DT_I4)column : NULL(DT_I4)

    Requirement Example 

    Input

    1

    0

    2a

    .

    4

    Output

    1

    0

    null

    null

    4


    Wednesday, May 18, 2016 6:07 PM

Answers

  • Hi Rabindranath Ray,

    Based on my research, I think this also is a way which could achieve your requirement. (Reference below)

    1. Convert the records to (DT_I4) with a Derived Column.
    2. Redirect the records that couldn’t be converted to (DT_I4) to another Derived Column, and convert them to NULL(DT_I4).
    3. Merge the records that are transferred from the two Derived Column Transformations by Union ALL Transformation.

    If you have any other questions, please feel free to post.

    Regards,
    Seif Wang



    • Edited by Seif Wang Thursday, May 19, 2016 3:25 AM
    • Proposed as answer by Harry Bal Thursday, May 19, 2016 7:26 PM
    • Marked as answer by Rabindranath Ray Monday, May 23, 2016 6:31 AM
    Thursday, May 19, 2016 3:23 AM

All replies

  • Hi, as a first step, just check for the presence of the dot. And make it a NULL. Can be part of the same expression, nested.

    Arthur

    MyBlog


    Twitter

    Wednesday, May 18, 2016 9:12 PM
    Moderator
  • Hi Rabindranath Ray,

    Based on my research, I think this also is a way which could achieve your requirement. (Reference below)

    1. Convert the records to (DT_I4) with a Derived Column.
    2. Redirect the records that couldn’t be converted to (DT_I4) to another Derived Column, and convert them to NULL(DT_I4).
    3. Merge the records that are transferred from the two Derived Column Transformations by Union ALL Transformation.

    If you have any other questions, please feel free to post.

    Regards,
    Seif Wang



    • Edited by Seif Wang Thursday, May 19, 2016 3:25 AM
    • Proposed as answer by Harry Bal Thursday, May 19, 2016 7:26 PM
    • Marked as answer by Rabindranath Ray Monday, May 23, 2016 6:31 AM
    Thursday, May 19, 2016 3:23 AM
  • SSIS expression does not have equivalent of ISNUMERIC function like we have in TSQL. So my suggestion would be to check in data source rather than using expression. Moreover we don't know what type of value will appear in "column" field. Why should we limit only for "." and what if the it appears as decimal?

    Regards, RSingh

    Thursday, May 19, 2016 4:00 AM
  • Thank you, Seif. 
    Monday, May 23, 2016 6:32 AM
  • Hi ArthurZ, 

    Thanks for your input. I already tried it but it did not work.

    Thanks,

    Rabindra

    Monday, May 23, 2016 6:34 AM