Very slow query and I was a bit confused by the "remote scan"…
I was as confused by the "Remote Scan" because its accessing a local table.
I should also add this is from a SSIS package.
The query optimizer uses a fixed guess of 10,000 rows for the number of rows in the file. This estimate is used to reserve memory for the Sort operators in the plan. This reserved memory cannot be expanded once query execution begins, regardless of how much free memory your SQL Server may have available.
Your file presumably contains very many more rows than 10,000 so the sort operations run out of sort memory space and repeatedly "spill" sort runs to physical tempdb disk. This is a very slow operation. The repeated spilling explains why you see a "sawtooth" I/O pattern.
There are no details about the process or tables in the anonymized question, so offering solutions requires a little guesswork. Nevertheless:
If the number of rows being inserted represents a large fraction of the rows in the destination table – or if the destination is empty – you could drop all the indexes before inserting, and recreate them afterward.
This will eliminate the sorts in the BULK INSERT plan, and replace them with sorts based on a known data size in index build plans, which may use minimal-logging if the conditions are right (see Books Online and/or The Data Loading Performance Guide for details).
If the destination table is too large for that technique to work well, you could still BULK INSERT to a heap table, and then move the rows into the destination. Bulk insert to a heap is fast and may be minimally logged. The move to the destination table can also be optimized, and will at least know the number of rows involved for any sorting.
Supported and documented trace flag 610 may help achieve performance gains – see the Data Loading Performance Guide link given earlier for details.
There are other query tuning tricks that could be employed here but there are too many conditions and caveats to list them all without knowing more about the specific circumstances.