none
Conversion of data is SSIS

    Question

  • Hello !

    I am having an issue converting an INT data type to a Decimal data type in SSIS using a Derived Column transform.

    This is what I have done:

    "Booked  <add as new column>  (DT_DECIMAL,6)Booked  decimal [DT_DECIMAL]      6  "

    II think the issue is the scaling part. ..(DT_DECIMAL, «scale») . I am loading to a column that looks like this:

    "Length DECIMAL(6,2) Not Null" And the source data type is an int.

    How do I overcome this?

    Thanks for the help.


    Zionlite

    Friday, July 12, 2013 10:38 AM

Answers

  • You'll need to cast the int column as well:

    (ISNULL(Length) ? (DT_NUMERIC,5,2)0 : (DT_NUMERIC,5,2)Length)

    However, numeric(5,2) is too small to contain integers. 123456 is a normal integer (and not that big), but this will be casted to 123456.00 and this already doesn't fit your numeric column.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Yookos Friday, July 12, 2013 11:55 AM
    Friday, July 12, 2013 11:45 AM

All replies

  • It should be (DT_DECIMAL,2) or (DT_NUMERIC,6,2). Do you get any errors?

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Friday, July 12, 2013 10:57 AM
  • Thanks for the reply. I just tried both but I'm still getting the same errors.

    What I did:

    Length  <add as new column>  (ISNULL(Length) ? (DT_DECIMAL,2)0 : Length)  numeric [DT_NUMERIC]    29  2  

    then I tried:

    Length  <add as new column>  (ISNULL(Length) ? (DT_NUMERIC,5,2)0 : Length)  numeric [DT_NUMERIC]    12  2  

    and the error:

    "Invalid character value for cast specification".
    "Conversion failed because the data value overflowed the specified type.".

    Thanks for the help


    Zionlite

    Friday, July 12, 2013 11:21 AM
  • Which data type has the Length column?

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Friday, July 12, 2013 11:27 AM
  • The Length column is the one from the source and it's of int data type. I want to cast it into a table with a Length column but with a decimal data type(Decimal 6,2)


    Zionlite


    • Edited by Yookos Friday, July 12, 2013 11:41 AM mjjjjj
    Friday, July 12, 2013 11:40 AM
  • You'll need to cast the int column as well:

    (ISNULL(Length) ? (DT_NUMERIC,5,2)0 : (DT_NUMERIC,5,2)Length)

    However, numeric(5,2) is too small to contain integers. 123456 is a normal integer (and not that big), but this will be casted to 123456.00 and this already doesn't fit your numeric column.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Yookos Friday, July 12, 2013 11:55 AM
    Friday, July 12, 2013 11:45 AM