Home Dashboard Directory Help
Search

SSIS OLE DB Source incorrectly returns zero records in combination with parameter and comment by Valentino Vranken


Status: 

Active


5
0
Sign in
to vote
Type: Bug
ID: 773689
Opened: 12/6/2012 9:12:45 AM
Access Restriction: Public
1
Workaround(s)
view
2
User(s) can reproduce this bug

Description

Given the right circumstances, the SSIS OLE DB Source component in the Data Flow will return no records at all, although it should actually return records.

The bug occurs when the OLE DB Source uses at least one parameter and has a comment line right above the WHERE clause. The comment should be one using a double-dash. In short, this results in FMTONLY being set to ON while running the query and thus results in zero records.

More details: http://blog.hoegaerden.be/2012/12/05/ssis-ole-db-source-parameters-and-comments-a-dangerous-mix/

I should also mention that this bug no longer occurs in SQL Server 2012.
Details
Sign in to post a comment.
Posted by Koen Verbeeck on 12/6/2012 at 1:26 PM
I can reproduce this as well. It's not the most annoying bug, but it can cause you to scratch your hair for a few hours while you're wondering where all your rows went.
Sign in to post a workaround.
Posted by Valentino Vranken on 12/6/2012 at 9:16 AM
If you face this issue, the easiest workaround is to use a block comment instead of the "remainder of line" comment, like this:

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
/* some even smarter comment goes here */
where Color = ?