2

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
2
  • I have the same issue. Have you found a solution for this? – Serge Jun 19 '18 at 4:32
  • @Serge No, I never found a resolution to the error. I ended up splitting my one query into two different queries and staging tables in my SQL Server and worked with the data there. Would love to know why this was happening though. – ARich Jun 19 '18 at 4:36
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.