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?

I have seen a problem in the past which may be related. In my case, the version of .NET being used by SSDT appeared to be a later version than...

Your variable @source is not being set, hence the NULLs.

Package properties don't affect the bitness when set up as an Agent job.

Instead, to run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use...