Ok, more data points...

My source is SQL Server.  I'm copying data from ServerSrc to ServerTgt.  I have no rights on ServerSrc other than query data.

My parameters...

FYI, connect.microsoft.com is retired.  https://docs.microsoft.com/en-us/collaborate/connect-redirect.  Search on "SQL Server".

Use this instead:  https://feedback.azure.com/forums/908035-sql-server.

@All:
From my post above:
"My source table is on a remote server. I have no rights on that server - I can only read data, can't create views, sp's,...

Thanks for all the replies.  I've been pretty busy so apologies for the delayed follow up post.

"Have you tried building the SQL in an expression and using that...

tl;dr:  Is WITH (NOLOCK) always bad practice?

Well kind of disappointing that there's no comments or advice on this post, but it happens.  I acknowledge that could certainly be...

Thanks for all the replies...nah, just kidding.  Damn, I must ask the really hard questions 😉

The first view is now:

CREATE VIEW [dwh].[vwDIAGNOSIS]
AS
--...

So I found this SO post:  https://stackoverflow.com/questions/42782464/error-in-ssis-parameters-cannot-be-extracted-when-parsing-query-syntax-error

Which echoes my original sentiment:  SSDT is braindead.

Nesting the CTE doesn't work either:

SELECT *
FROM (
  SELECT *, ROW=ROW_NUMBER() OVER (
   PARTITION BY facility_identifier, stay_number, episode_sequence_number
   ORDER BY...

Perhaps I could but I don't want to.  If dtexec is so braindead that it can't execute a file system package ala SSDT, then I'll just deploy the project, run...

After posting my OP, I found this:  https://stackoverflow.com/questions/31388748/ssis-project-connections  (which is nowhere in the documentation)

But dtexec /package FACILITY.dtsx /project '.\bin\Development\RLDX - Tmp.ispac'

Just flashes a command window, says the...

(This post is morphing into best practice for code maintenance, rather than the original topic, but let's run with it...)

Background:
I have 30 staging tables with the same...

@scott Coleman

Thanks for the reply.