SSIS unpivot returns different results when run via a SQL agent job
I think I've fixed it. Thought I'd share the answer in case anyone else comes across this exact same issue. All it took was the addition of IMEX=1 to the Excel connection string. 

I stumbled across this post which explains more: Why SSIS always gets Excel data types wrong, and how to fix it!