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

Question

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

Hall of Fame

Points: 3458

February 28, 2024 at 7:42 pm

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)

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

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

Phil Parkin SSC Guru Points: 246819 More actions · Answer 1

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?

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

yrstruly Hall of Fame Points: 3458 More actions · Answer 2

yrstruly

Hall of Fame

Points: 3458

February 28, 2024 at 8:44 pm

#4386650

These are the sort of values in the Excel: ssis04

Phil Parkin SSC Guru Points: 246819 More actions · Answer 3

Try changing to scale 19, precision 0.

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

This reply was modified 4 months, 4 weeks ago by Phil Parkin.

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

frederico_fonseca SSCoach Points: 16088 More actions · Answer 4

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.

Phil Parkin SSC Guru Points: 246819 More actions · Answer 5

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.

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