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.
[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?
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 -
These are the sort of values in the Excel:
-
Try changing to scale 19, precision 0.
Also, is the destination column really a 510 character string?
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 -
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.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply