Excel Source [24]] Error: System.Exception: SqlTruncateException:

  • Please assist. I am using SSIS to read data from an Excel sheet into Postgres. I have increased the column size a few times, just cant seem to get the data in. Getting errors relating to destination column size.

    ssis01

    ssis02

    ssis03

     

    [Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes truncation.. RowNumber=1, ColumnName=Metric Value [DataType=DT_NUMERIC,Length=0], Data=[Length=12, Value=311116655.63]
    at ZappySys.PowerPack.Adapter.SqlDataComponentBase.HandleException(Exception ex)
    at ZappySys.PowerPack.Adapter.SqlDataComponentBase.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)
  • Precision 19, scale 18 works for numbers like

    1.123456789012345678

    ie, 1 number before the decimal and 18 after. Are your numbers in this format?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • These are the sort of values in the Excel:ssis04

  • Try changing to scale 19, precision 0.

    Also, is the destination column really a 510 character string?

    • This reply was modified 1 month, 2 weeks ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 19, 0 not enough as the data being truncated has at least 2 decimal places according to the error.

    to the OP - if you don't know what the size should be do a initial load as text, then look at ALL the values including the number of decimal places and then define the correct size allowing for potentially 2 extra digits of precision.

  • frederico_fonseca wrote:

    19, 0 not enough as the data being truncated has at least 2 decimal places according to the error.

    Missed that, thanks.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 6 posts - 1 through 5 (of 5 total)

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