Search

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

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)
2
User(s) can reproduce this bug
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 (expand)

Product Language

English

Version

SQL Server 2008 R2 SP1

Category

Integration Services (DTS)

Operating System

Windows 7 Ultimate (SP1)

Operating System Language

US English

Steps to Reproduce

o create a new SSIS package
o add a package variable, type String, and set it to "Blue"
o add an OLE DB Connection Manager (Provider=SQLNCLI10.1) that connects to AdventureWorksDW (from CodePlex)
o add a Data Flow
o add an OLE DB Source that uses the connection manager and the following query:

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = ?

o use the Parameters button to connect Parameter0 with User::Color
o connect a Union All to the source and run the package

Actual Results

Zero records go down the Data Flow.

Expected Results

28 were expected to go down the Data Flow.

This can be proven by running the following query through SSMS:

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = 'blue'

Platform

X64

Virtualization

 
File Attachments
0 attachments
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 = ?
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement