this linky shows how you can peel the orange with a chainsaw:
instead of simply downloading all the projects and using a text editor to search the *.dtsx files, this...

here's what i did: narrow down what table it could possibly be:
SELECT
OBJECT_SCHEMA_NAME([colz].object_id),
...

i seem to remember that the package_data column of the SSISDB.[internal].[packages] is actually an encrypted value stored as a varbinary columns, so you cannot cleanlyconvert it to search the xml...

can you check to see if the .Net 3.5 library was installed? unless you hand edit a config file, you could install say, SQL2014 or 2016, and db mail does...

i have a job scheduled on each server that contains a reportserver database that updates the job description with the report name, so it's much easier to track down that...

certainly possible, but something like that sounds like it needs to be part of the dynamic generation processes, and not a cleanup after the fact.
the problem is we don't...

sys.dm_exec_sessions has whether a spid/session_id is a user process or a system process.

thios seems to work for me, is this what you are after?

SELECT...

I would think the log would be useless, as it has what was successfully committed, and is not a log of errors and what did NOT get inserted.

you...

jayoub i think you have to avoid using sqlcmd in this case, and create a simple SSIS package isntead.
I knew two things right away that were affecting you:
you...

I believe the issue is the Service Principal Names (spn) on one or both of the servers in question is not set up, so the Windows credentials do not get...

your Table structure has all the columns in brackets, so that the name with spaces doesn't break the CREATE TABLE command..
your Foreign keys simply need to have the same logic.

Doing a best guess at Foreign keys depends on the design and proper naming conventions.
for example, do all the tables have primary keys? are they properly named? (ie TableNameID...

The SQL Error Log has what you are looking for
Every time an instance is restarted, the previous log is closed and marked as archived, and the new one is...

Hashbytes is a varbinary(8000) return value, so you need a larger column, and then you are all set.
you do NOT need to filter or edit your columns for dashes...