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

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)

StackExchange Database Administrators Q#55383, answer score: 10