September 28, 2018 at 9:18 am
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply