@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, functions, etc."
And where do you suggest I store these views, stored procedures, etc???????

@Phil Parkin:
"...embedding complex SQL logic in an SSIS package is a bad idea."
Please answer whether you think these queries are simple or complex.  A yes or no answer is fine (and preferred):

1) SELECT * FROM MYTABLE
2) SELECT * FROM MYTABLE WHERE valid_from_date BETWEEN ? AND ?  (where ? is an SSDT parameter)
3) SELECT x.* FROM (SELECT *, ROW=ROW_NUMBER() OVER (PARTITION BY whatever ORDER BY whatever) FROM MYTABLE WHERE valid_from_date BETWEEN ? AND ?) AS x WHERE ROW=1

IMO, the correct answer is yes for all three.  If you think a simple nested query with a windowing function is "complex", well, I don't know what to say on that.  BTW, all queries would work fine - SSDT wouldn't consider it "complex" - if I just remove the parameters.

@Thom_A:
"Have to agree with Phil here; almost all of my SSRS and SSIS packages use SP's for their interactions with the database."
How do you handle the situation where you need to extract data from ServerA (source) to ServerB (target), and you have no rights on ServerA (other than reading the data)?
BTW, once my data is on my local server, all the rest of my SSDT processing is using views and stored procedures to encapsulate the required logic.  SSDT is just the "glue" or "plumbing" to package up and execute the code.

"I'm guessing, perhaps, you mean it performs badly, or perhaps you're saying that the SQL doesn't actually work (at all)."
I think I explained this clearly in my later post.  The SQL doesn't actually work at all (even complains when saving it).

"You mention that you would need to use a linked server,"
Forget I ever mentioned linked server.
IIIIIFFFFF I had a linked server, one approach (not saying I would use it) would be to use the linked server + a view on my local server to encapsulate the logic and to pull the data across the network (via the linked server) to my local server. 

@Tim.ffitch:
"your best option is to create a stored procedure and use EXECUTE WITH RESULTSETS"
See above re: access rights on the remote server.
I'll read up on EXECUTE WITH RESULTSETS...thanks.

"I believe the problem is that when using a CTE SSIS is not able to determine the Schema hence you have to define it in your Execute statement."
I disagree.  I tested this on a small table using SQL Command in the OLEDB data source:

WITH cteLastRow AS (
SELECT *
FROM (
SELECT *, ROW=ROW_NUMBER() OVER (
PARTITION BY facility_identifier
ORDER BY replica_valid_from_date DESC)
FROM dbo.FACILITY) AS src
WHERE ROW=1
)
SELECT * FROM cteLastRow

This works fine.

BTW, THIS IS JUST AN EXAMPLE.  Phil, if you comment about SELECT *, or it's too complex, or whatever, I'll "scream".  It's just an example that SSDT and OLEDB data source can handle such a hideously complex query as the data source.

But this fails:

WITH cteLastRow AS (
SELECT * 
FROM (
SELECT *, ROW=ROW_NUMBER() OVER (
PARTITION BY facility_identifier
ORDER BY replica_valid_from_date DESC)
FROM dbo.FACILITY) AS src
WHERE ROW=1
)
SELECT * FROM cteLastRow WHERE replica_valid_from_date > ?

Doesn't even save.

I'm happy to receive further comments, but I remain convinced SSDT is braindead with respect to parameter parsing.  In fact, while I appreciate all the comments, not a single one addressed my assertion that SSDT parameter parsing is broken.  Instead, they all essentially say "don't do that", rather than addressing the subject as stated.

I'm thinking this post is about run its course...

I'll Google how to enter bugs on SSDT (I think it's Microsoft Connect???) and see if MS have anything to say (not holding my breath on that one).