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 ?

Here are quick steps to reproduce this issue:

  • In Adventure Works DW database create new table based on the script

IF OBJECT_ID(’dbo.tmpKey’) IS NOT NULL DROP TABLE tmpKey;
CREATE TABLE tmpKey (CurrencyKey int NOT NULL);

This will be our destination table and you can delete this table after this test.

  • Start BIDS and create new SSIS project and then SSIS package.
  • Add OLE DB connection to Adventure Works DW database. Name it DB for simplicity.
  • Add “Execute SQL” task to control flow. Change connection to DB and SQL Statement to:

IF OBJECT_ID(’dbo.tmpKey’) IS NOT NULL DROP TABLE tmpKey;
CREATE TABLE tmpKey (CurrencyKey int NOT NULL);

This way we will be able to run package multiple times.

  • Add two variables to package:
    Name: MinID Type: Int32 value 1
    Name: MaxID Type: Int32 value 2
  • Add data flow task and connect it to go after “Execute SQL” task.
  • Double click on data flow task to go into data flow area.
  • Add “OLE DB source” and “OLE DB destination” components. Make connection from “OLE DB Source” to “OLE DB Destination”
  • Change “OLE DB source” properties:
      Connection: DB
      Data Access Mode: SQL Command
      SQL Command text:
      SELECT CurrencyKey
        FROM dbo.DimCurrency
      WHERE CurrencyKey BETWEEN ? and ?
  • For “OLE DB Source” component click on “Parameters” button and map parameters:
      Parameter0  User::MinID
      Parameter1  User::MaxID
  • Change “OLE DB Destination” properties
      Connection: DB
      Data access mode: “Table or view - fast load”
      Name of the table or the view: “dbo.tmpKey”
      Click on Mappings and map “CurrencyKey” to “CurrencyKey”
  • Add data viewer to see records moving between “OLE DB Source” and “OLE DB Destination”
  • Save and execute package. You will see 2 rows moved between source and destination. That is correct behaviour.
  • Now change “OLE DB Source” SQL command text to:
    SELECT CurrencyKey
      FROM dbo.DimCurrency
    – my comment line
    WHERE CurrencyKey BETWEEN ? and ?
  • Save and execute package. No data will be moved. That I believe is a bug.

I submitted this issue to Microsoft connect website:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=298046

Of course, workaround for this problem is to remove line that starts with comments.

Mar 14th, 2008 update. Microsoft reply:

The problem that you have described is an issue with the SQL Server Native Client provider. This problem occurs with a parameterized SQL query using in SSIS components such as the OLE DB Source, when the query contains a comment line that begins with the comment characters “–”. The OLE DB Source does not parse the SQL statement (because it must support the varying SQL syntax of various data sources) and cannot catch the problem. The only solution is to omit comments from parameterized SQL statements used in the OLE DB Source.