Data Flow Task Produces No Rows with Declare Statement
Well, even though I don't actually know the reason behind this behavior, I remember reading about this some time ago, and what to do to fix it. To make this work, you just need to add SET NOCOUNT ON as the first line of your sql command:

SET NOCOUNT ON
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)