Fabric Copy Job Timeout

Hi joel_delima​ 

We would like to inquire whether have you got the chance to check the solutions provided by other users in community to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Hi joel_delima​,

this looks like a gateway-side timeout, not an issue with your Copy Job logic itself. A few things point that way:

• The error source is Microsoft.DataTransfer.DataContracts, which is the data movement/gateway layer, and "Dispose resource timeout" typically means the connection/resource couldn't be cleanly closed within the allotted time, common when a large initial load keeps a connection open far longer than smaller incremental runs.
• It only fails on the first run on large tables (100M+ rows) because incremental runs after that only move the delta (via CDC), much smaller payloads, so the gateway connection doesn't stay open nearly as long.

Likely causes and fixes:

1. On-prem Data Gateway timeout settings - the gateway has its own internal query timeout separate from the Copy Job's settings. Check Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config on the gateway machine for timeout values (similar to the MashupDefaultPoolContainerMaxWorkingSetInMB type settings), these often need to be raised for very large initial loads.
2. Gateway resource exhaustion - 100M+ row initial loads can exhaust memory/connections on the gateway VM over a 40-minute window. Check gateway machine resource usage (CPU/RAM) during the run.
3. Split the initial load - rather than relying on the Copy Job to pull the full 100M+ rows in one shot, do a one-time manual bulk load (e.g., via a Pipeline Copy activity with partitioning, or BCP/bulk insert) to seed the table, then let the Fabric Copy Job take over for incremental CDC sync going forward. This sidesteps the timeout entirely for the problematic first run.

Option 3 is generally the most reliable fix reported for "first full load fails, incremental works fine" patterns with CDC-based pipelines.

Hi joel_delima​,

Fabric is not exactly intended to be used to move data from one on prem system to another on prem system. 

When this big table is running, what do your resources on the gateway server look like? Are you maxing out your CPU and memory? If so you may need to upgrade your gateway server to add more power.

What you could also try is to split the job into two steps, one to ingest the data from the 2008 SQL into a Fabric datastore like a SQL Database, then a second job to take it from the Fabric database back to your on prem SQL 2025 server. 

If your SQL Servers are on the same network, you can also skip fabric directly and create a linked server or external table on the 2025 server that points to the 2008 server and use cross server queries to load the data, this is the approach I would take first. 

Hi joel_delima​,

I would be a little careful about treating the ~40-minute failure as a documented Copy Job timeout.

Microsoft does support SQL Server through an on-premises data gateway for Copy Job, including Full, Incremental and CDC scenarios, so the architecture itself is valid.

The error:

System.TimeoutException
Dispose resource timeout
Source=Microsoft.DataTransfer.DataContracts

does suggest the transfer/gateway path is timing out or failing to release a resource, but I could not find a Microsoft document that defines a fixed 40-minute timeout for Copy Job.

Given that only the first 100M+ row load fails, I would check:

1. Gateway CPU, memory and connection usage during the run.
2. Whether the gateway is on the latest supported version.
3. SQL Server query duration and blocking while the initial full read is running.
4. Whether the failure consistently happens at the same elapsed time or only under heavy gateway load.

For a table this large, I would also consider doing the initial load with a Fabric Pipeline Copy activity instead of Copy Job.

The SQL Server Copy activity supports query timeout and source partitioning including dynamic-range partitioning for parallel reads.

A practical pattern would be:

initial 100M+ row load
-> Pipeline Copy activity with partitioned reads

then

ongoing changes
-> Copy Job CDC / incremental copy

That gives you more control over the expensive first load while still keeping the simpler incremental process afterward.

If the job still fails at roughly the same point with low gateway CPU/memory and no SQL blocking, I would capture the Copy Job run ID, gateway logs, exact timestamp and full exception and raise a Fabric support case.

I would not start modifying undocumented gateway timeout settings unless Microsoft Support specifically recommends it.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi joel_delima​,

One thing about this setup that hasn't come up yet: both your source and your target are on-premises. For the initial load, every one of those 100M+ rows travels from the SQL 2008 server through the gateway up to the Fabric service, then back down through the gateway to SQL 2025:

SQL 2008 (on-prem) -> gateway -> Fabric -> gateway -> SQL 2025 (on-prem)

That round trip is the most likely reason a single long-running read/write session hits a resource timeout around 40 minutes, especially on the first full copy. The incremental runs move far less data, which is why they are fine.

I would split the problem in two:

initial load   -> make it smaller per run, or keep it on-prem
ongoing CDC    -> keep using the Copy job

Option A: stay in Fabric, but partition the initial load. Use a pipeline Copy activity for the first load with dynamic range partitioning, so it runs as several shorter, parallel reads instead of one long session:

"source": {
  "type": "SqlServerSource",
  "partitionOption": "DynamicRange",
  "partitionSettings": {
    "partitionColumnName": "Id",
    "partitionLowerBound": "1",
    "partitionUpperBound": "120000000"
  }
},
"parallelCopies": 8

Pick an indexed integer or date column for partitionColumnName, and keep parallelCopies modest so the gateway and the 2008 server aren't overloaded.

Option B: seed the big tables on-prem. Since both servers sit on your network, a native bulk copy in key-range chunks avoids the cloud round trip entirely, and each chunk is short enough to restart on its own:

rem export a 10M-row range from SQL 2008 (native format)
bcp "SELECT * FROM SourceDb.dbo.BigTable WHERE Id BETWEEN 1 AND 10000000" ^
    queryout BigTable_001.dat -S SQL2008 -T -n

rem load it into SQL 2025 in 100k-row batches
bcp TargetDb.dbo.BigTable in BigTable_001.dat ^
    -S SQL2025 -T -n -b 100000 -h "TABLOCK"

After seeding, check how your Copy job handles a target that already has data before switching it on for those tables. If it insists on a full initial copy, Option A is the safer route.

In both cases, watch the gateway machine's CPU and memory during the run. The gateway does the format conversion for both legs here, so it works twice as hard as in a normal cloud-destination load.

So my overall approach would be:

recognize the on-prem -> cloud -> on-prem round trip
-> initial load: partitioned Copy activity, or on-prem bcp seed
-> ongoing changes: Copy job with CDC as today
-> support case with run ID if chunked loads still time out

That keeps the large first load out of a single long-running session, which is where this timeout comes from.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi joel_delima​ 

We would like to inquire whether have you got the chance to check the solutions provided by other users in community to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.