Sql-server – Oracle CTE to SQL Server Using SSIS OLE DB Source

cteoledboraclesql server 2014ssis

I wrote a against an Oracle database and the query executes without issue when working in Oracle SQL Developer. I need to use to transport the results of that query to a SQL Server database.

In I'm using a data flow task, with an OLE DB Source and an OLE DB Destination component. In my source component I'm using the SQL Command data access mode.

When I paste my query into the command text window and click preview I see the error No column information was returned by the SQL command.

Other threads I've seen seem to indicate that this is possible, so I'm wondering if something in my configuration is incorrect. All fields have names, and I can execute other queries against this data source. Is there something I'm overlooking here?

For clarification purposes, my query basically looks like this:

WITH CTE(column1,column2) AS (
    SELECT field1 --window function
           ,field2
    FROM table1
)

SELECT column1
       ,column2
FROM CTE
WHERE condition1 is true

Best Answer

I had the same problem, which I managed to resolve by updating the Oracle driver.

My environment:

  • SSDT 11.1.20627.00 running on Visual Studio 2012 Update 4
  • Oracle OLEDB driver 11.2.0.1 - this version was not working with CTE

I updated the Oracle OLEDB driver to 12.2.0.1 and the error disappeared. I can now use CTE's in the packages:

Screenshot of OLEDB Source Editor showing results for query with a CTE

The Oracle OLEDB driver is part of Oracle Data Access Components package, which can be downloaded from Oracle (32-bit version, 64-bit version).

Tip 1: To find your current Oracle OLEDB driver version, locate the OraOLEDBnn.dll (where nn is a number, eg 11, or 12) file in the Oracle directory. Open file properties and look for File version under Details tab.

Tip 2: If you have a mess in the Oracle directory (eg multiple directories; old versions present), use the NirSoft RegDllView tool to show a list registered DLL's. The separate 32 and 64 build versions of the tool will show 32 and 64 bit DLL's, respectively.

Related Question