About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

MVP Logo

Search

Report Portal
blank

SSIS: Issue with OLE DB Source component that is based on parametrized SQL Query

September 13th, 2007 by Vidas Matelis

While writing SSIS package to load one of my data warehouse tables, I encountered SSIS problem that took me some time to figure out. I noticed that in the dataflow task one of the OLE DB source components was not returning any data even I know that records were there. So I did some investigation and found what was causing this issue.

I found that when following conditions are met, OLE DB Source component will not return any data:

  • “OLE DB Source” component is based on the SQL Query
  • SQL Query contains parameters
  • SQL Query contains line that starts with comments symbol “–” 

 Example, this query (database “Adventure Works DW”) works:

SELECT CurrencyKey
  FROM dbo.DimCurrency
WHERE CurrencyKey BETWEEN ? AND ?

But this query always returns empty result set:

SELECT CurrencyKey
FROM dbo.DimCurrency
— my comments
WHERE CurrencyKey BETWEEN ? AND ?

Read the rest of this entry »

Posted in SQL Server, SSIS | 12 Comments »

SSAS 2008 Katmai review – installation and first impressions

June 10th, 2007 by Vidas Matelis

This weekend I had some time to play with SQL Server 2008 (Katmai) June CTP, and I am posting here my first impressions (focus on Analysis Services).

Installation

As a first attempt I tried to setup SQL Server 2008 on the computer that already had SQL Server 2005 default instance installed. I wanted to install SQL 2008 as a named instance and compare SQL 2005 and 2008 side by side. After answering few standard questions I got message that because SQL Server 2005 was found on the machine, SQL Server 2008 cannot install client tools. I proceed with installation without client tools. After installation I tried to use SQL Server 2005 BIDS environment and connect to Katmai instance, but somehow that did not work. Then finally I decided that maybe I should read readmesqlkatmai.htm file. Found that side by side installation is not supported. From readme file: Read the rest of this entry »

Posted in SQL Server, SSAS, SSAS 2008 - Katmai | 2 Comments »

XL Cubed

Vardecimal storage usage in SQL Server 2005

March 25th, 2007 by Vidas Matelis

With SQL Server 2005 Service Pack 2 Microsoft introduced storage format vardecimal. As I work a lot with big fact tables, I decided to test how this new format affects storage. If you want to get estimated impact on using vardecimal storage format, you can run stored procedure sp_estimated_rowsize_reduction_for_vardecimal. This SP expects table name as parameter and returns row count, average row length for fixed format and average row length for vardecimal format.

Read the rest of this entry »

Posted in SQL Server | Comments Off on Vardecimal storage usage in SQL Server 2005