You misunderstood my suggestion.

Create an...

Build your dynamic SQL statement as an SSIS variable and then, within your ExecuteSQL task, set the SQLSourceType property to 'Variable' and designate your derived SQL variable as the 'SourceVariable'...

Can you at least prove...

Are you saying that you do not know the name of the proc that was executed 27 times (and if so, I'd be interested in knowing what you are looking...

The error message seems clear. The connection from the SQL Server instance to the target server is different from the one from your machine. It could be a networking issue,...

My JSON handling is pretty dodgy, but the following query gets you part of the way.

DECLARE @x NVARCHAR(MAX)
    = N'{
    "metadata":...

I believe the more common, and better, naming for tables is plural.  I can't think of a major relational dbms that uses singular system table names.

For example: sys.objects (SQL...

I agree with the advice given so far, and have a couple more ideas for you to consider.

1. Had you named your tables in the singular (Person, Community), your junction...

I don't think there's a way to hide it, so you might have to tolerate it. Or embark on a rewrite.

This is nothing to worry about. After deploying your package to a server, no one will be there to see the dialog box during execution.

Do you report all times in UTC instead? If not, what do you...

Why not create a separate dataset which runs that exact query for you?

Could it be that the 2022 box is using TLS 1.2+ and the others are using a lower (and now invalid) version?

Use environment variables to control the values of connection strings and other parameters which vary between environments.

Here is a link to get you started: https://www.mssqltips.com/sqlservertip/4810/setup-environment-variables-in-sql-server-integration-services/

OK, then consider putting all of your data into a single (wide) temp table, adding a 'GroupNo' column for each dataset. Filter on the GroupNo within SSRS to split out...