I can't execute that 🙂

I mean like this

DROP TABLE IF EXISTS #Personnel

CREATE TABLE #Personnel (<column definitions>)

INSERT #Personnel
VALUES (),(),() --your sample data

--Same for #PersonnelTeam and #Team

Doing it like...

Are you able to provide your sample data in a format which we can cut & paste into SSMS, please?

That looks good to me.

This should help you dig into the hierarchy.

SELECT returns.id
      ,calc_chan_detail.channel
      ,calc_chan_detail.name
FROM
    OPENJSON(@json)
  ...

The...

Did you make any progress with this?

That file structure does not look easy to process. Your idea of converting the source data to JSON would certainly make it easier to ...

The silence is deafening.

Well done on solving it. There's no shame in this, it was a learning experience 🙂

Maybe use checksum() as suggested & push results to a separate 'duplicate suspects' table, then refine your search using the data in the new table?

Unless Z is a local drive on the server, those are not UNC paths. You should avoid mapped drives when doing this.

SSMS v18 and onwards do not appear to have the debugging feature. However, it is still possible to debug T-SQL, but using SSDT as your debug client.

Try using the full UNC path, eg:

\\servername\c$\folder\filename

(after verifying that the user running the job can access that path)

Also check that your proxy is being picked up properly by checking the...

Well done.

Now, how about fixing whatever it is that puts that garbage into your Audit Trail table in the first place? 🙂

You're right, it's probably not the same issue. But it was the only thing I could think of which was at all similar.

Lastly is getting SQL Server using the same...

Does [ProvidersDirectory].[dbo].[ProvidersTable] have the source data column?

If so, use that.

If not, where does the source data come from?