Data Factory Copy Activity is failing for Hubspot data source

Question

A Data Factory Copy Activity, sourcing data from a HubSpot connector and sinking to a CSV file in a data lake, is consistently failing with a data type conversion error on the source side.

The pipeline is attempting to copy data without an explicitly defined schema, causing Data Factory to infer data types incorrectly.

Error Code: UserErrorWriteFailedFileOperation

• Root Exception: Type=System.InvalidCastException
• Message: Value '' for column 'amount' cannot be converted to Double.,Source=Microsoft.DI.Driver.HubSpot
• Pipeline Component: Failure is occurring on the 'Source' side during the data conversion/staging process before the write operation.

The failure is specifically tied to the 'amount' column from the HubSpot source.

1. Schema Inference: Data Factory's default schema inference samples the HubSpot data and determines the 'amount' column should be a Double (numeric) type.
2. Invalid Data: The HubSpot data contains records where the 'amount' field is an empty string ('').
3. Conversion Failure: The copy engine attempts to cast the empty string ('') to the inferred Double type, which results in the fatal InvalidCastException.
4. The following steps were attempted within the Copy Activity settings to resolve the issue, but the UI prevented modification of the inferred source type:

Attempted to manually set the Source Type for the amount column from Double to String in the Copy Activity Mapping tab but the type cannot be edited.

Attempted to clear and re-import the mapping. Failed again.

Attempted to use a json file type as the sync dataset and that failed.

Answer 1

Hi @Peter Blatchley
Welcome to the Microsoft Q&A and thank you for posting your questions here.
As pr over observation root cause is still source-side type inference: HubSpot marks amount numeric, but some rows send amount as an empty/dirty string, so the source driver attempts “” → numeric and fails before the write phase. Any non-numeric string (empty/whitespace, “NA”, currency symbols, thousands of separators, etc.) can trigger the cast error on the source step.

What you can do (pick one)

1. Keep the run green and capture rejects Copy activity → Settings → Fault tolerance → enable “Skip incompatible rows” and log error rows to ADLS (errorDataSettings). You’ll get clean data in the sink plus a reject file to triage the bad amount values.
2. Clean the values before casting (recommended) Add a Mapping Data Flow (or a small post-copy cleaning step) and normalize amount to a safe decimal:

amount_clean =
iif(
  isNull(amount) || trim(toString(amount))=='' ||
  toString(amount) in ('NA','N/A','NaN','NULL','null','—','-'),
  null(),
  toDecimal(
    replace(
      replaceRegex(toString(amount), '[^0-9\\-\\.,]', ''),  -- drop currency/symbols
      ',', ''                                              -- drop thousands separator
    )
  )
)

Sink amount_clean as DECIMAL with explicit precision/scale.

3. “Stringify then cast” ingestion (driver-agnostic) If you need to avoid the HubSpot driver’s typing altogether, pull the same endpoint via the generic REST connector (JSON) and land raw strings to staging. In a second step (data flow or SQL), cast to DECIMAL using the expression above.

Extra checks & corner cases to watch

• Values that are only spaces/tabs, “0 ” with trailing space, or negatives in parentheses like “(123.45)”.
  • If using regex, strip parentheses for negatives first: replaceRegex(amount,'[()]','') before cast.
• Locale commas “1.234,56” vs “1,234.56” — normalize before toDecimal.
  • Confirm your regex pattern matches your locale rules; [^\d\-\.,] works for en-US.
• Mixed shapes across pages (missing property vs empty string).
• Oversized numeric exceeding your DECIMAL precision/scale (treat as NULL or widen the target).

If you’re on the older connector, consider upgrading; newer versions map “number” to Decimal and change a few options.

If you’re still blocked, please share required details in private message:

• One redacted failing payload (showing amount exactly as received),
• Run ID, connector version, a screenshot of Copy → Source settings and more
• Desired DECIMAL precision/scale in the sink. I’ll suggest the exact expression/precision based on that. Reference link:
  • https://learn.microsoft.com/en-us/azure/data-factory/connector-hubspot?wt.mc_id=knowledgesearch_inproduct_azure-cxp-community-insider&tabs=data-factory#dataset-properties
  • https://learn.microsoft.com/en-us/azure/data-factory/connector-hubspot?source=recommendations&tabs=data-factory