I've only seen completed jobs/steps in the job history table.

Most of sql agent happens outside of sql server. Take a look at sp_help_job. I think it gets job status...

Best to rethink the process so it's not a huge transaction.

If you can't do that, try a workaround to the workaround. Temporarily load the 90mb xml in chunks ...

Triggers need to be short and sweet to minimized transaction time and blocking. Creating a Word doc definitely does not apply.

Try having trigger add a row to a queue...

As far as I know, you can only use a column alias (dte) in an "order by" clause. You need to duplicate the case statement in the month() and year(),...