pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes
5+ messages / 3 participants
[nested] [flat]

* Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes
@ 2024-02-28 20:22  Anthony Apollis <anthony.apollis@gmail.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Anthony Apollis @ 2024-02-28 20:22 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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)

Attachments:

ssis01.png (image/png, 162.6K) download view image
ssis02.png (image/png, 152.7K) download view image
ssis03.png (image/png, 145.8K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes
@ 2024-02-28 20:50  Anthony Apollis <anthony.apollis@gmail.com>
  parent: Anthony Apollis <anthony.apollis@gmail.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Anthony Apollis @ 2024-02-28 20:50 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Data in Excel

On Wed, 28 Feb 2024 at 22:22, Anthony Apollis <anthony.apollis@gmail.com>
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.
>
> [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)
>
>

Attachments:

ssis04.png (image/png, 14.9K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes
@ 2024-02-29 00:12  Erik Wienhold <ewie@ewie.name>
  parent: Anthony Apollis <anthony.apollis@gmail.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Erik Wienhold @ 2024-02-29 00:12 UTC (permalink / raw)
  To: Anthony Apollis <anthony.apollis@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes
@ 2024-02-29 14:59  Erik Wienhold <ewie@ewie.name>
  parent: Erik Wienhold <ewie@ewie.name>
  0 siblings, 1 reply; 5+ messages in thread

From: Erik Wienhold @ 2024-02-29 14:59 UTC (permalink / raw)
  To: Anthony Apollis <anthony.apollis@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes
@ 2024-03-01 13:15  felix.quintgz@yahoo.com
  parent: Erik Wienhold <ewie@ewie.name>
  0 siblings, 0 replies; 5+ messages in thread

From: felix.quintgz@yahoo.com @ 2024-03-01 13:15 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org


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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2024-03-01 13:15 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-28 20:22 Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Anthony Apollis <anthony.apollis@gmail.com>
2024-02-28 20:50 ` Anthony Apollis <anthony.apollis@gmail.com>
2024-02-29 00:12 ` Erik Wienhold <ewie@ewie.name>
2024-02-29 14:59   ` Erik Wienhold <ewie@ewie.name>
2024-03-01 13:15     ` felix.quintgz@yahoo.com

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox