SSIS OLE DB Source, Parameters And Comments: A Dangerous Mix!
Once again I’ve been wasting some time because of a silly bug. This time it was due to the OLE DB Source component and the way it works with parameters. If you are in a situation where you know your query is working fine and yet no records are going down the data flow, here’s a possible solution!
Disclaimer: this issue exists up until SQL Server 2008 R2. Read on for details!
Update: after being advised to do so by several people, including Jamie Thomson, I’ve filed a bug at MS Connect: SSIS OLE DB Source incorrectly returns zero records in combination with parameter and comment
I had a Data Flow with an OLE DB Source that uses one parameter, for instance:
select ProductAlternateKey, EnglishProductName from dbo.DimProduct --some really smart comment goes here where Color = ?
I knew the query was working fine because when executed through SSMS and with the question mark replaced with ‘blue’, it would return 28 rows:
But when executed in BIDS, through either Execute Package or Execute Task, it would return zero records:
So I thought something must be going wrong with the package variable that gets passed into the source parameter, somehow. I’m not going into details on what I tried out in my attempt to get this working, but I can tell you that I started to get really irritated. My colleague Koen Verbeeck (b|t) can confirm this because I called him over to my desk to help me think! (thanks btw!)
After some further tinkering with the data flow, we had our smart moment of the day and decided to launch SQL Server Profiler to see what BIDS was sending to the server! I’m not sure if you’re aware of this but BIDS is doing some metadata-related stuff when preparing queries. As far as I can tell, it also tries to determine the parameter type by running the following query:
set fmtonly on select Color from dbo.DimProduct --some really smart comment goes here where 1=2 set fmtonly off
When creating this statement, it seems to use the whole FROM clause of the original query, including any trailing comments. It combines that with a SELECT statement that contains the field that gets filtered and it appends " where 1=2 set fmtonly off".
But alas, apparently it’s not aware that lines can be commented out by using a double dash. So part of its generated statement is commented out. What it should have done is used some CRLFs, especially in front of the WHERE clause. But it didn’t.
So, as a result of that, FMTONLY remains on while the SELECT statement gets executed, resulting in zero records!
For those unfamiliar with the FMTONLY setting:
Returns only metadata to the client. Can be used to test the format of the response without actually running the query.
And I can actually confirm what I’m stating here by changing the query to the following:
set fmtonly off; select ProductAlternateKey, EnglishProductName from dbo.DimProduct --some really smart comment goes here where Color = ?
28 records down the pipe!
But this hack is a little too dirty to put in production. So what else can we do? Well, use block-style comments instead and we won’t face the issue!
select ProductAlternateKey, EnglishProductName from dbo.DimProduct /* some even smarter comment goes here */ where Color = ?
So, as I mentioned at the start of the post, this behavior can be reproduced using SSIS versions prior to 2012. What about 2012 then? Here’s the result of the Data Flow using the first query mentioned above:
Alright, that works better! Now let’s use Profiler to check what’s going on here. This is the first statement that gets executed:
exec [sys].sp_describe_undeclared_parameters N'select ProductAlternateKey, EnglishProductName from dbo.DimProduct --some really smart comment goes here where Color = @P1'
Further down, I also see this one:
exec [sys].sp_describe_first_result_set N'select ProductAlternateKey, EnglishProductName from dbo.DimProduct --some really smart comment goes here where Color = @P1',N'@P1 nvarchar(15)',1
It is using an entirely different approach, no longer using the FMTONLY setting! Hang on, this rings a bell! Look what the BOL page for SET FMTONLY (2012 version) specifies:
Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).
If you’re not on SQL Server 2012 yet, be careful with comments in OLE DB Sources in the SSIS Data Flow! Ow, and get the SQL Server Profiler off its dusty shelf now and then!
- T-SQL Tuesday 50: Automation, Automation, Automation!
- The "Select ALL" parameter option [SSRS]
- Book: SQL Server 2012 Reporting Services Blueprints
- SQL Server Days 2013 – Data Cleansing: Download
- SQL Server Days 2013
- Exploring the System.Object Package Variable [SSIS]
- T-SQL Tuesday 46: Contraptions!
- Creating Multi-Column Reports: The Top-Down Version [SSRS]
- Formatting Dates [SSRS]
- Community Day – Data Visualization Tips & Tricks: Download
- January 2014 (1)
- December 2013 (1)
- November 2013 (2)
- October 2013 (1)
- September 2013 (2)
- July 2013 (2)
- June 2013 (2)
- May 2013 (3)
- March 2013 (3)
- February 2013 (2)
- January 2013 (2)
- December 2012 (2)
- November 2012 (3)
- October 2012 (2)
- August 2012 (2)
- July 2012 (2)
- June 2012 (2)
- May 2012 (2)
- April 2012 (3)
- March 2012 (4)
- February 2012 (4)
- January 2012 (2)
- December 2011 (2)
- November 2011 (2)
- October 2011 (1)
- September 2011 (3)
- August 2011 (2)
- June 2011 (2)
- May 2011 (3)
- April 2011 (3)
- March 2011 (3)
- February 2011 (2)
- January 2011 (5)
- December 2010 (1)
- November 2010 (3)
- October 2010 (3)
- September 2010 (2)
- August 2010 (4)
- July 2010 (2)
- June 2010 (4)
- May 2010 (6)
- April 2010 (3)
- March 2010 (3)
- February 2010 (11)
- January 2010 (9)
- December 2009 (2)
- November 2009 (3)
- October 2009 (3)
- September 2009 (4)
- August 2009 (6)
- July 2009 (2)
- June 2009 (3)
- May 2009 (7)
- April 2009 (3)
- March 2009 (3)
- February 2009 (5)
- January 2009 (4)
- December 2008 (2)
- November 2008 (3)
- October 2008 (1)
- September 2008 (1)
- August 2008 (4)
- July 2008 (3)