While writing SSIS package to load one of my\u00a0data warehouse tables, I\u00a0encountered\u00a0SSIS problem that took me some time to figure out.\u00a0I 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.<\/p>\n
I found that when following conditions are met, OLE DB Source component will not return any data:<\/p>\n
\u00a0Example, this query (database “Adventure Works DW”) works:<\/p>\n
SELECT CurrencyKey
\n\u00a0 FROM dbo.DimCurrency
\nWHERE CurrencyKey BETWEEN ? AND ?<\/p><\/blockquote>\nBut this query always returns empty result set:<\/p>\n
SELECT CurrencyKey
\nFROM dbo.DimCurrency
\n— my comments
\nWHERE CurrencyKey BETWEEN ? AND ?<\/p><\/blockquote>\n<\/p>\n
Here are quick steps to reproduce this issue:<\/p>\n
\n
- In Adventure Works DW database create new table based on the script<\/li>\n<\/ul>\n
\nIF OBJECT_ID(‘dbo.tmpKey’) IS NOT NULL DROP TABLE tmpKey;
\nCREATE TABLE tmpKey (CurrencyKey int NOT NULL);<\/p><\/blockquote>\n<\/blockquote>\nThis will be our destination table and you can delete this table after this test.<\/p><\/blockquote>\n
\n
- Start BIDS and create new SSIS project and then SSIS package.<\/li>\n
- Add OLE DB connection to Adventure Works DW database. Name it DB for simplicity.<\/li>\n
- Add “Execute SQL” task to control flow. Change connection to DB and SQL Statement to:<\/li>\n<\/ul>\n
\nIF OBJECT_ID(‘dbo.tmpKey’) IS NOT NULL DROP TABLE tmpKey;
\nCREATE TABLE tmpKey (CurrencyKey int NOT NULL);<\/p><\/blockquote>\n<\/blockquote>\nThis way we will be able to run package multiple times.<\/p><\/blockquote>\n
\n
- Add two variables to package:
\nName: MinID Type: Int32 value 1
\nName: MaxID Type: Int32 value 2<\/li>\n- Add data flow task and connect it to go after “Execute SQL” task.<\/li>\n
- Double click on data flow task to go into data flow area.<\/li>\n
- Add “OLE DB source” and “OLE DB destination” components. Make connection from “OLE DB Source” to “OLE DB Destination”<\/li>\n
- Change “OLE DB source” properties:
\n\u00a0 Connection: DB
\n\u00a0 Data Access Mode: SQL Command
\n\u00a0 SQL Command text:
\n\u00a0 SELECT CurrencyKey
\n\u00a0\u00a0\u00a0 FROM dbo.DimCurrency
\n\u00a0 WHERE CurrencyKey BETWEEN ? and ?<\/li>\n- For “OLE DB Source” component click on “Parameters” button and map parameters:
\n\u00a0 Parameter0\u00a0 User::MinID
\n\u00a0 Parameter1\u00a0 User::MaxID<\/li>\n- Change “OLE DB Destination” properties
\n\u00a0 Connection: DB
\n\u00a0 Data access mode: “Table or view – fast load”
\n\u00a0 Name of the table or the view: “dbo.tmpKey”
\n\u00a0 Click on Mappings and map “CurrencyKey” to “CurrencyKey”<\/li>\n- Add data viewer to see records moving between “OLE DB Source” and “OLE DB Destination”<\/li>\n
- Save and execute package. You will see 2 rows moved between source and destination. That is correct behaviour.<\/li>\n
- Now change “OLE DB Source” SQL command text to:
\nSELECT CurrencyKey
\n\u00a0 FROM dbo.DimCurrency
\n— my comment line
\nWHERE CurrencyKey BETWEEN ? and ?<\/li>\n- Save and execute package. No data will be moved.\u00a0That I believe is a bug.<\/li>\n<\/ul>\n
I submitted this issue to Microsoft connect website:<\/p>\n