pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / 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> 2024-02-28 20:50 ` Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Anthony Apollis <anthony.apollis@gmail.com> 2024-02-29 00:12 ` Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Erik Wienhold <ewie@ewie.name> 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 imagessis02.png (image/png, 152.7K) download
view image^ permalink raw reply [nested|flat] 5+ messages in threadssis03.png (image/png, 145.8K) download
view image
* Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes 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> 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:^ permalink raw reply [nested|flat] 5+ messages in threadssis04.png (image/png, 14.9K) download
view image
* Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes 2024-02-28 20:22 Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Anthony Apollis <anthony.apollis@gmail.com> @ 2024-02-29 00:12 ` Erik Wienhold <ewie@ewie.name> 2024-02-29 14:59 ` Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Erik Wienhold <ewie@ewie.name> 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-28 20:22 Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Anthony Apollis <anthony.apollis@gmail.com> 2024-02-29 00:12 ` Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Erik Wienhold <ewie@ewie.name> @ 2024-02-29 14:59 ` Erik Wienhold <ewie@ewie.name> 2024-03-01 13:15 ` Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes felix.quintgz@yahoo.com 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-02-28 20:22 Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Anthony Apollis <anthony.apollis@gmail.com> 2024-02-29 00:12 ` Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Erik Wienhold <ewie@ewie.name> 2024-02-29 14:59 ` Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes Erik Wienhold <ewie@ewie.name> @ 2024-03-01 13:15 ` felix.quintgz@yahoo.com 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