SQL Server – Data Flow Task Produces No Rows with Declare Statement

I have a SSIS package that is doing a simple SELECT statement and then loading into a table. I have anonymized the sql statement which should be painfully obvious when you look at the table name. I have also simplified the package to just do a row count for the purposes of this demo.

This issue I've having is that the query produces rows in SQL server but produces no rows in SSIS. The query is as follows.

DECLARE @CurrentBlahDate DATETIME
SET @CurrentBlahDate = ( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' ))
SELECT 
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(@CurrentBlahDate AS DATE)

Please ignore the * as that is just for the purposes of this demo. The actual query has the same issue even though it does not have the *. When Executed in SQL server this returns 1 row from the calendar table as expected. However when executed against the same environment in SSIS I get no rows returned.

If I turn the query into a stored procedure and execute the sproc in SSIS I get a row count of 1.

If I alter the query to this.

SELECT 
*
FROM dbo.Calendar
WHERE CAST(BaseDate AS DATE) = CAST(( SELECT MAX(BlahDate)
                                         FROM   dbo.ThisCoolTable SL
                                                WITH ( NOLOCK )
                                                INNER JOIN dbo.ThatCoolTable SLF
                                                WITH ( NOLOCK ) ON SL.CoolID = SLF.CoolID
                                                              AND SLF.TypeCode IN (
                                                              'ValueA',
                                                              'ValueB' )) AS DATE)

Then I once again receive 1 row.

Is there any reason I'm seeing this behavior?

UPDATE:
Before I receive a ton of flack for this I have no choice on the NOLOCK nonsense.