Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

SSIS

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

1Row

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.

1 More 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.

share|improve this question
4  
I'd give you flack over the ` CAST(BaseDate AS DATE)` personally... –  billinkc Dec 20 '13 at 16:17
    
Lol once again this is obfuscated. –  Zane Dec 20 '13 at 18:11
add comment

2 Answers

up vote 8 down vote accepted

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)
share|improve this answer
    
This works great thanks a ton. However if anyone happens to have an explanation for this I would be happy to hear it. –  Zane Dec 20 '13 at 18:04
add comment

Lamak has answered the question.

The reason might be that during the package run time, the OLEDB will process the first resultset. So when the command contains a variable, the DONE_IN_PROC messages will be returned as empty resultsets. So the OLEDB source will take the first resultset and will proceed further which will lead the package to succeed but without any rows.

As Lamak pointed out the SET NOCOUNT ON will suppress sending the DONE_IN_PROC messages to the client.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.