"No, the Client doesn't like stored procedures so we have done all our TL parts of the ETL using a bunch of views on top of views on top of views."

Wish I could have been here at the start so I could have pushed back, sigh.

Siiiggghhhh, yet the client is anal about performance and even consistency in SSIS packages.....siiiggghhhhhh but we dont have SHOWPLAN permissions or even sp_who2 access...siiiigggghhhhhh.

If i expanded one of the final views, would be like 1k lines. For the amount of data, we move, there shouldn't be any noticeable processing time but it can take anywhere from 10mins to an hour.