Numeric arithmetic causes truncation.

• below86

  SSChampion

  Points: 11578

  More actions

  September 28, 2018 at 9:18 am

  #377831

  I like to say I know enough VB to be dangerous, and I'm tired of taking a shot in the dark on this.

  I'm getting this error in a VB Script in my SSIS package "Numeric arithmetic causes truncation."  

  Here is the other error info:
  at System.Data.SqlTypes.SqlDecimal.ConvertToPrecScale(SqlDecimal n, Int32 precision, Int32 scale)
   at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetDecimal(Int32 columnIndex, Decimal value)
   at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)
   at ScriptComponent_0fe48cc5de8b4affb69109a43e348d11.vbproj.ScriptMain.SetPrevYear(Input0Buffer row)
   at ScriptComponent_0fe48cc5de8b4affb69109a43e348d11.vbproj.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
   at ScriptComponent_0fe48cc5de8b4affb69109a43e348d11.vbproj.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
   at ScriptComponent_0fe48cc5de8b4affb69109a43e348d11.vbproj.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

  Here is the section of VB that it is erring in:  
  
  Public Sub SetPrevYear(ByVal row As Input0Buffer)
   reserveAdjustment = 0
   row.CTDOSRSRVAM = row.CTDOSRSRVAM + row.CurrCaseResv
   row.CTDPAIDAM = row.CTDPAIDAM + row.CtdNetPd
   row.AlaeCtdPaidAm = row.AlaeCtdPaidAm + row.CtdNetExp
   ultLossAmt = row.ULTINCLOSSAM
   AlaeUltLossAm = row.AlaeUltIncLossAm
   ctdReserve = row.CTDOSRSRVAM
   ctdPaid = row.CTDPAIDAM
   AlaeCtdPaid = row.CtdNetExp
   outstandingIbnr = row.OSIBNRAM
   Allocation = 0
   adtvPercent = row.ADTVFC
   ultIbnrPercent = row.ULTIBNRPC
   ultAdtvPercent = row.ULTADTVPC
   reserveAdjustment = Math.Round(ultLossAmt - ctdReserve - ctdPaid, 2, MidpointRounding.AwayFromZero)
   Allocation = (reserveAdjustment - outstandingIbnr)

     If reserveAdjustment <> 0 Then
    testult = Fix((outstandingIbnr * 100) / reserveAdjustment) 'added Fix
    If testult > 999.9 Or testult < -999.9 Then
      ultIbnrPercent = CDec(999.9)          'added CDec
      ultAdtvPercent = CDec(999.9)          'added CDec
    Else
      ultIbnrPercent = Fix(testult * 10) / 10
      ultAdtvPercent = CDec(100.0 - ultIbnrPercent)    'added CDec
    End If
   End If

     If ctdReserve = 0 Then
    adtvPercent = 0
    Allocation = 0

     Else
    If Math.Round((Allocation / ctdReserve), 6, MidpointRounding.AwayFromZero) > 9.999999 Then  'added round
      adtvPercent = CDec(9.999999)           'added CDec
      Allocation = Fix(adtvPercent * ctdReserve * 100) / 100
    Else
      If Math.Round((Allocation / ctdReserve), 6, MidpointRounding.AwayFromZero) < -9.999999 Then 'added round
       adtvPercent = CDec(-9.999999)         'added CDec
       Allocation = Fix(adtvPercent * ctdReserve * 100) / 100
      Else
       adtvPercent = Fix(Allocation / ctdReserve * 1000000) / 1000000
      End If
    End If
   End If

     If Allocation < 0 Then
    ultIbnrPercent = 0
    ultAdtvPercent = 0
   End If

     If adtvPercent = 0 Then
    Allocation = 0
   End If

     row.RSRVADJAM = CDec(reserveAdjustment)
   row.ADTVALLOCATIONAM = CDec(Allocation)
   row.ULTADTVPC = CDec(ultAdtvPercent)
   row.ULTIBNRPC = CDec(ultIbnrPercent)
   row.ADTVFC = CDec(adtvPercent)
   row.AlaeRsrvAdjAm = row.AlaeUltIncLossAm - row.AlaeCtdPaidAm

     If ctdReserve = 0 Then
    row.AlaeAdtvFc = 0
    row.AlaeAdtvAllocationAm = 0
   ElseIf (row.AlaeUltIncLossAm - row.AlaeCtdPaidAm - row.AlaeIbnrReserveAm) / row.CTDOSRSRVAM > 9.99 Then
    row.AlaeAdtvFc = CDec(9.999999)
    row.AlaeAdtvAllocationAm = Fix(row.AlaeAdtvFc * row.CTDOSRSRVAM * 100) / 100
   ElseIf (row.AlaeUltIncLossAm - row.AlaeCtdPaidAm - row.AlaeIbnrReserveAm) / row.CTDOSRSRVAM < -9.99 Then
    row.AlaeAdtvFc = CDec(-9.999999)
    row.AlaeAdtvAllocationAm = Fix(row.AlaeAdtvFc * row.CTDOSRSRVAM * 100) / 100

     Else
    row.AlaeAdtvFc = Fix((row.AlaeUltIncLossAm - row.AlaeCtdPaidAm - row.AlaeIbnrReserveAm) / row.CTDOSRSRVAM) 'added Fix
    row.AlaeAdtvAllocationAm = row.AlaeUltIncLossAm - row.AlaeCtdPaidAm - row.AlaeIbnrReserveAm
   End If

    End Sub

  I've noted the things I've tried with the 'added  comment.

  I totaled the input data and CurrCaseResv is just over $200,000,000.  CtdNetPd is $2,800,000,000 and CtdNetExp is also just over $200,000,000.  This same process ran with the same data earlier this month in production, not sure why it would be erring when I'm trying to test it.  I didn't change anything in this script, before getting the error.

  Any help you can give to identify what piece of the code is causing the issue would be appreciated.

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.