Thread: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes

On 2024-02-28 21:22 +0100, Anthony Apollis wrote:
> 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.

What is the data type of that column?

> [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)

Not sure what I'm looking at, but screenshots 1 and 2 say precision=19
and scale=18 which would be numeric(19,18).  But value 311116655.63
shown in the error message requires a precision that is at least 9
digits larger than the scale:

    regress=# select '311116655.63'::numeric(19,18);
    ERROR:  numeric field overflow
    DETAIL:  A field with precision 19, scale 18 must round to an absolute value less than 10^1.
    regress=# select '311116655.63'::numeric(27,18);
           numeric
    ------------------------------
     311116655.630000000000000000
    (1 row)

-- 
Erik

cc'ing list.

On 2024-02-29 07:16 +0100, Anthony Apollis wrote:
> I cant change data type using the advance editor in destination:
> [image: image.png]
> [image: image.png]

The column type must be changed in the database itself.

On second thought, are you sure "Metric Value" is the right destination
column for your import?  Maybe type numeric(19,18) is intentional.

> On Thu, 29 Feb 2024 at 08:11, Anthony Apollis <anthony.apollis@gmail.com>
> wrote:
> 
> > I tried multiple data types, including: "Metric Value" text COLLATE
> > pg_catalog."default", "Metric Value" character varying(510) COLLATE
> > pg_catalog."default", and Decimal/Numeric.

Did you get the same error with the text columns?  In plain SQL you can
certainly insert numeric into text columns.  Postgres does an implicit
cast in that case.  Not sure what your middleware does in that case
though.

> > On Thu, 29 Feb 2024 at 02:12, Erik Wienhold <ewie@ewie.name> wrote:
> >
> >> On 2024-02-28 21:22 +0100, Anthony Apollis wrote:
> >> > 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.
> >>
> >> What is the data type of that column?
> >>
> >> > [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)
> >>
> >> Not sure what I'm looking at, but screenshots 1 and 2 say precision=19
> >> and scale=18 which would be numeric(19,18).  But value 311116655.63
> >> shown in the error message requires a precision that is at least 9
> >> digits larger than the scale:
> >>
> >>         regress=# select '311116655.63'::numeric(19,18);
> >>         ERROR:  numeric field overflow
> >>         DETAIL:  A field with precision 19, scale 18 must round to an
> >> absolute value less than 10^1.
> >>         regress=# select '311116655.63'::numeric(27,18);
> >>                    numeric
> >>         ------------------------------
> >>          311116655.630000000000000000
> >>         (1 row)

-- 
Erik

The numeric(19, 18) field type can only store numbers less than 10 with 18 decimal places.
To save the number 311116655.63 you need a numeric(12, 2) field type.


 On Thursday, February 29, 2024 at 10:00:10 AM GMT-5, Erik Wienhold <ewie@ewie.name> wrote:

 cc'ing list.

On 2024-02-29 07:16 +0100, Anthony Apollis wrote:
> I cant change data type using the advance editor in destination:
> [image: image.png]
> [image: image.png]

The column type must be changed in the database itself.

On second thought, are you sure "Metric Value" is the right destination
column for your import?  Maybe type numeric(19,18) is intentional.

> On Thu, 29 Feb 2024 at 08:11, Anthony Apollis <anthony.apollis@gmail.com>
> wrote:
>
> > I tried multiple data types, including: "Metric Value" text COLLATE
> > pg_catalog."default", "Metric Value" character varying(510) COLLATE
> > pg_catalog."default", and Decimal/Numeric.

Did you get the same error with the text columns?  In plain SQL you can
certainly insert numeric into text columns.  Postgres does an implicit
cast in that case.  Not sure what your middleware does in that case
though.

> > On Thu, 29 Feb 2024 at 02:12, Erik Wienhold <ewie@ewie.name> wrote:
> >
> >> On 2024-02-28 21:22 +0100, Anthony Apollis wrote:
> >> > 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.
> >>
> >> What is the data type of that column?
> >>
> >> > [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)
> >>
> >> Not sure what I'm looking at, but screenshots 1 and 2 say precision=19
> >> and scale=18 which would be numeric(19,18).  But value 311116655.63
> >> shown in the error message requires a precision that is at least 9
> >> digits larger than the scale:
> >>
> >>        regress=# select '311116655.63'::numeric(19,18);
> >>        ERROR:  numeric field overflow
> >>        DETAIL:  A field with precision 19, scale 18 must round to an
> >> absolute value less than 10^1.
> >>        regress=# select '311116655.63'::numeric(27,18);
> >>                    numeric
> >>        ------------------------------
> >>          311116655.630000000000000000
> >>        (1 row)

--
Erik