error with numeric columns in a PostgreSQL connection via ADF integration runtime 5.64.9558.1

Question

The decimal numbers are multiplied with this version of the integration runtime. How do i fix this?

Answer 1

Hi Arinda Dollekamp,
Thanks for raising this, I completely understand how concerning it is to see numeric values getting altered during the copy process.

From what you’ve described, this behavior (decimal values appearing multiplied) does not look like a mapping or pipeline configuration issue. It aligns with a scenario we’ve seen in certain Self-hosted Integration Runtime (SHIR) builds used by Azure Data Factory, where numeric and decimal values from PostgreSQL are not handled correctly during data movement and may be scaled unexpectedly. Since this occurs within the runtime’s internal data handling layer, before mapping or transformations are applied, changes at the pipeline level typically do not resolve the issue.

The most reliable mitigation is to move the Self-hosted Integration Runtime back to a previously stable version where this behavior was not observed. This would involve uninstalling the current SHIR build (for example, 5.64.x), installing the last known working version, and re-registering it using the existing authentication key so it shows as “Online” under Manage > Integration runtimes in Azure Data Factory. Once the runtime is back online, you can re-run the pipeline and validate that the numeric values are processed correctly.

You can refer to the official documentation here:

https://learn.microsoft.com/azure/data-factory/create-self-hosted-integration-runtime https://learn.microsoft.com/azure/data-factory/self-hosted-integration-runtime-auto-update

If changing the SHIR version is not feasible right now, a temporary workaround would be to ingest the affected numeric columns as string and cast them back to numeric at the destination, or to use Mapping Data Flows with explicit schema and type handling to control precision and scale. These approaches can help avoid incorrect scaling but are not intended as permanent fixes.

The workaround implemented was to cast the affected numeric column to varchar during the connector operation and then cast it back to int in the later processing logic. This helped avoid the conversion/parsing issue encountered with the PostgreSQL connector while preserving the intended data type handling in downstream logic.

Answer 2

Hello Arinda !

Thank you for posting on MS Learn Q&A.

I don't think you have an issue with PostgreSQL but with your SHIR and as a workaround you can roll back the SHIR version as mentioned in this old thread https://learn.microsoft.com/en-us/answers/questions/5626895/error-with-numeric-columns-in-a-postgresql-connect

For version 5.64.9558.1 you need to upgrade first to the newer SHIR version 5.65.9593.1.

https://www.microsoft.com/en-us/download/details.aspx?id=39717

and if the issue remains simply downgrade to a previous stable SHIR version for example 5.62.9520.4 or 5.61.9481.1.

Then register again the SHIR using the same authentication key and rerun a small test copy with a known value for example 123.45.

Answer 3

Decimal or numeric issues in Azure Data Factory pipelines are commonly caused by mismatches between source/target data types or how null/empty values and precision/scale are handled during copy.

For numeric/decimal columns being multiplied or otherwise changed when moving data via Integration Runtime:

1. Verify precision/scale definitions
   • Check the PostgreSQL column definitions (NUMERIC/DECIMAL precision and scale).
   • Check the corresponding sink column definitions (for example, in Azure SQL, Synapse, or SQL Server) and ensure they match the same precision and scale.
   • Mismatched precision/scale can cause rounding or unexpected numeric transformations.
2. Check for empty-string or null handling in numeric columns
   • When using staged copy/PolyBase into Synapse or SQL-based sinks, empty strings in numeric columns can cause errors or unexpected behavior.
   • If using PolyBase in the sink, set use type default to false in the copy activity’s PolyBase settings so that empty strings are not forced into numeric defaults that could distort values.
3. Review transformations in the pipeline
   • Inspect Mapping Data Flows, Derived Columns, or any expressions that might be multiplying or scaling numeric values (for example, implicit casts, arithmetic operations, or default values).
   • Remove or correct any transformation that changes the numeric scale unintentionally.
4. Validate with a minimal test
   • Create a small test table in PostgreSQL with known numeric values.
   • Run a simple copy activity with no transformations and compare source vs. sink values to identify where the multiplication occurs (source, IR, or sink).

If the issue persists only with a specific Integration Runtime version, capture a minimal reproducible pipeline and open a support case so that the behavior can be compared against expected numeric handling.

---

References:

• Troubleshoot the Azure Synapse Analytics, Azure SQL Database, SQL Server, Azure SQL Managed Instance, and Amazon RDS for SQL Server connectors in Azure Data Factory and Azure Synapse
• Troubleshoot self-hosted integration runtime
• KB2929832 - FIX: Decimal precision is incorrect when you calculate based on size of scale in SQL Server 2012 or SQL Server 2014 MDS
• KB3097970 - FIX: Sp_describe_undeclared_parameters returns incorrect result when the undeclared parameter targets a decimal column
• how to fix decimal code in sql - Microsoft Q&A
• Column Data size issue - Microsoft Q&A
• Not able to find the proper issue field - Microsoft Q&A
• How do I fix an SQL Server issue - Microsoft Q&A
• I just cant find a fix to this error. - Microsoft Q&A