DF incorrectly maps Oracle NUMBER(18) to BIGINT instead of DECIMAL(18,0), causing SqlDecimal-to-BIGINT conversion failures

[acodato-bi-analyst]

Summary

When using Azure Data Factory’s Auto-Creat​e Table feature with Oracle as the source and SQL Server as the sink, ADF inconsistently maps Oracle numeric types.

• Oracle NUMBER(38) → correctly mapped to DECIMAL(38,0)
• Oracle NUMBER(18) → incorrectly mapped to BIGINT

This is unsafe because ADF internally reads Oracle NUMBER values as SqlDecimal, which cannot always be converted to BIGINT. This directly causes pipeline failures during copy operations.

---

Actual Behavior

ADF creates the SQL table with:

ACCOUNT_ID BIGINT

But during ingestion, ADF reads Oracle NUMBER as SqlDecimal and fails with:

The given value '526797' of type SqlDecimal cannot be converted to type bigint.

This matches previously documented ADF behaviors where numeric conversion between source decimals and SQL numeric types fails when precision/scale are not compatible. [learn.microsoft.com]
Additionally, Oracle NUMBER fields may contain internal decimal precision even when declared as integer-like, which is known to cause precision issues during ADF Oracle→SQL migrations and requires workarounds (CAST, ROUND, TO_CHAR) to avoid failures. [github.com],


Expected Behavior
ADF should map Oracle NUMBER(18) to DECIMAL(18,0), not BIGINT.
Why?

DECIMAL(18,0) is fully compatible with Oracle NUMBER semantics.
It avoids ingestion failures when ADF receives SqlDecimal values.
It preserves precision safely.
The performance difference vs BIGINT is negligible.
DECIMAL has no practical drawbacks in SQL Server for integer data.
ADF already maps NUMBER(38) to DECIMAL(38,0) — so mapping NUMBER(18) the same way ensures consistency.

Impact
This inconsistency breaks large-scale dynamic ingestion pipelines using:

dynamic datasets
metadata-driven loops
auto-create table

Auto-create cannot be reliably used because ADF may arbitrarily choose BIGINT even when DECIMAL is the correct and safe type.
This forces users to implement workarounds such as:

dynamic CASTing in SELECT statements
TO_CHAR conversions
manual schema overrides
disabling auto-create entirely

These are unnecessary if ADF simply mapped NUMBER(18) → DECIMAL(18,0).

Repro Steps

Source: Oracle table with column ACCOUNT_ID NUMBER(18)
Sink: SQL Server
Enable “Auto-create table”
Run ADF Copy activity
Observe:
Sink column created as BIGINT
Copy activity fails with SqlDecimal → BIGINT conversion errors

Request
Please update the type-mapping behavior:
Proposed mapping change:

From:  NUMBER(p) where p ≤ 19 and scale=0 MAPS TO bigint
To:      NUMBER(p) where p ≤ 19 and scale=0 MAPS TO decimal(p,0)

This makes the behavior safe, predictable, and consistent with how NUMBER(38) is handled.