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 are stored in a parameters table on ServerTgt.  Let's call them DateTimeStart and DateTimeEnd.  They are DATETIME2(0) format.  They are created by a stored process, which (usually) copies the previous DateTimeEnd to the new DateTimeStart, and the new DateTimeEnd is midnight of today.  I say usually because the SP allows a manual override in case of recovery situations or initial load.  (Not really germane to the problem...probably TMI)

I copy the parameters to SSDT variables via an SQL task and Single Row Resultset.

I'd previously Googled that DATETIME2 SQL Server columns had to be copied to System.Object SSDT variables.  I can't find that link(s) right now or I'd reference them.

However, changing the SSDT variable data type to DATETIME seems to work OK.  I'm sure I tried that when I first created my packages a couple months ago, and I can't remember what error message SSDT was spewing at the time.

Anyway, after further testing today, based on the information provided in last night's posts (i.e. "it works for me..."), I think I've encountered the crux of the problem.  Apologies I didn't find it earlier...

My Source server is Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

My Target server is Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

I've copied a small table from the Source to Target server.  It is identical to the source table, just on the target server.  The only difference is the schema in which it was saved.

This query from the source server fails:

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

If the only change I make is to change the connection manager to the target server, and add the schema to the query, it works:

SELECT *
FROM (
  SELECT *, ROW=ROW_NUMBER() OVER (
   PARTITION BY facility_identifier
   ORDER BY replica_valid_from_date DESC)
  FROM tmp.FACILITY
  WHERE ? <= replica_valid_from_date AND replica_valid_from_date < ?
) AS src
WHERE ROW=1

So, it seems to be server version related.  If any of you have SQL Server 2008 lying around I'd love to hear if you encounter the same issue.

BTW, I still think SSDT is braindead with respect to parameters:

http://sqlblog.com/blogs/jamie_thomson/archive/2011/03/14/have-ssis-differing-type-systems-ever-caused-you-problems.aspx
http://blog.hoegaerden.be/2011/06/11/passing-a-datetime-parameter-into-the-execute-sql-task/

Edit:

This query also fails in 2008 and works in 2012.  Nice trick to know, so thanks for the SO link.

DECLARE @DateTimeStart DATETIME2(0)=?;
DECLARE @DateTimeEnd DATETIME2(0)=?;

SELECT *
FROM (
  SELECT *, ROW=ROW_NUMBER() OVER (
   PARTITION BY facility_identifier
   ORDER BY replica_valid_from_date DESC)
  FROM FACILITY
  WHERE @DateTimeStart <= replica_valid_from_date AND replica_valid_from_date < @DateTimeEnd
) AS src
WHERE ROW=1