Sorry, I hadn't noticed that this was all being run locally, so no, network guy is the wrong person.

If, however, the OP can use DTExec to do it,...

Duplicate of https://www.sqlservercentral.com/Forums/1902189/SQL-Server-what-XML-part-of-query-is-doing-for-XML-experts

Sorry for late reply.

If you're using T-SQL to run the task, however, then I'm pretty confident that the problem is our old friend "Mr Double-hop" (Phil, would you...

You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

That's a handy little node Microsoft have added to SSMS (17.3). They've put "XE profiler" at the bottom of the objects for a serve. Effectively does the job of profiler...

You need to have permissions on the SQL Server instance, not on your local machine (the permmissions on your local machine are totally different to that on the instance).

I tend to do a create my own on when ordering, depending on what my mood is. Normally, however, the "mold" starts with Stuffed Crust, BBQ Base, Chillies (or Jalapeños...

Because you're trying to pass multiple rows to a single row UPDATE. This is much like doing:
CREATE TABLE #Sample (string char(1));
INSERT INTO #Sample
VALUES (NULL);

I'd hazard a that it's permissions. How are you running the task on your laptop, via agent, T-SQL or navigating to the SSIS Catalog?