Thanks Jeff.

I had checked what they were doing. It was actually 5 processes, all performing merge statements on the same table with different criteria on the WHERE Clause.

Their justification for doing this is that it will increase the bandwidth and speed up the load.

I have provided them with an abundance of CPU and memory.

I converted the OPENQUERY statements to OLE DB Source (AS400) and OLE DB Destination (SQL Server) and as expected the initial load to staging completed in a small fraction of the time it took to do an OPENQUERY.

Unfortunately we can't do that with the incremental on the large and they are doing MERGE OPENQUERY.

The problem is that most of the tables do not have Insert, Modified Date on them so they can't just select on the Datetime stamps.

On the smaller tables I converted their logic to a TRUNCATE & Fast Load OLE DB.

Edit: Typo on TRUNCATE