Have you checked for simple but lengthy blocking and deadlocks?

I'd also be concerned about the nearly flat 30% CPU usage you're seeing.

Just a couple of personal notes not covered in the article...

I certainly can't speak for anyone else but I find the ISDATE() function to have the same problems...

My recommendation would be to use an "External Data Source" in the spreadsheet rather than trying to write your own in VBA.

Other than that, you may have to...

NUMERIC(19,0) works just fine for this.  It does take 1 extra byte and a small bit of extra time compared to BIGINT wherever and whenever it's used but it works...

When you do your weekly dumps, are the IDs always larger than the largest ID in the dbo.Temp table or not?

Heh... and then there's string literals, which may also contain things like the word "Employee", which could be the name of a table.,

It would be much easier to help you resolve this if you provided some readily consumable test data.  See the first link in my signature line for how to do...

You can have a HEAP with a PK because a PK does not have to be a clustered index.

Shifting gears, yes... I've seen the problem that you describe...