Post reply

Data conversion failure in SSIS to FLOAT (DT_R8)

  • July 13, 2012 at 2:43 am

    #272325

    I have problem converting a value to a float, which I am readying from a text file.

    The value is: 92233720365847700

    SSIS gives following error when converting to DT_R8

    Error Column:8110: Conversion failed because the data value overflowed the type used by the provider.

    Derived column value looks like this:

    [Actual CDS-Bond Basis Mid] == "" ? NULL(DT_R8) : (DT_R8)[Actual CDS-Bond Basis Mid]

    Interestingly the data conversion works OK in SQL:

    DECLARE @Value FLOAT

    SELECT @Value=92233720365847700

    or

    SELECT @Value=CONVERT(FLOAT,92233720365847700)

    SELECT @Value

    Result: 9.22337203658477E+16

    So SQL can do the conversion but not SSIS. Any ideas short of truncating the value? Is this a genuine bug?

    Thanks for any ideas.

  • July 13, 2012 at 3:20 am

    #1512423

    Have you defined the column as double precision in your flat file source?

    If so, why do you talk about 'converting' to DT_R8?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 13, 2012 at 3:51 am

    #1512429

    Thanks for the reply.

    Field is coming in as string, because if I set the column to DT_R8 then blank values which are quoted "" which causes a failure.

    Hence why I have the code to convert "" to NULL(DT_R8). In my derived column.

  • July 13, 2012 at 4:01 am

    #1512431

    simoniainsmith (7/13/2012)

    Thanks for the reply.

    Field is coming in as string, because if I set the column to DT_R8 then blank values which are quoted "" which causes a failure.

    Hence why I have the code to convert "" to NULL(DT_R8). In my derived column.

    OK - so are you creating a string derived column which removes the "" and then running the output through a data conversion transformation?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 13, 2012 at 4:03 am

    #1512433

    Phil Parkin (7/13/2012)

    simoniainsmith (7/13/2012)

    Thanks for the reply.

    Field is coming in as string, because if I set the column to DT_R8 then blank values which are quoted "" which causes a failure.

    Hence why I have the code to convert "" to NULL(DT_R8). In my derived column.

    OK - so are you creating a string derived column which removes the "" and then running the output through a data conversion transformation?

    Yes as you can see here:

    [Actual CDS-Bond Basis Mid] == "" ? NULL(DT_R8) : (DT_R8)[Actual CDS-Bond Basis Mid]

  • July 13, 2012 at 4:26 am

    #1512442

    That does not look like a data conversion. After removing the quotes, I think you may need one of these.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 13, 2012 at 4:46 am

    #1512447

    Your right it is a derived column transformation, not a data conversion transformation. Will try that and see how it goes.

    Thanks

  • July 13, 2012 at 6:47 am

    #1512502

    Error: 0xC02020C5 at Staging_MarketFile, Data Conversion [12454]: Data conversion failed while converting column "Actual CDS-Bond Basis Mid" (3056) to column "Copy of Actual CDS-Bond Basis Mid" (12462). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    I tend not to use the data conversion because it is less flexible. In this case still results in error about loss of data.

Viewing 8 posts - 1 through 7 (of 7 total)

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