Why does DENSE_RANK cause an error when used in SSIS query against ORACLE using OLE DB?

1
votes

This query:

SELECT
    DENSE_RANK() OVER (PARTITION BY UPPER(ANUMID), UPPER(PRODNUMID) ORDER BY DATE_ADDED ASC) AS DRANK
,   ANUMID
,   PRODNUMID
,   STATUS_FDATE
,   STATUS_XDATE
,   ROWSTATUS
FROM
    AGCOMN

The query ranks the rows in each group of ANUMID, PRODNUMID by DATE_ADDED from 1 to x. In a subsequent query the DRANK=1 gets the most recent row added.

This query works in ORACLE SQL Developer, and on my local machine SSIS environment, and on the SSIS OLE DB Source preview on the TEST server, but does NOT work when the package is ran.

ERROR:

[OLE DB Source 1 [677]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ORA-00936: missing expression".

Environment:

  • Local - Windows XP, SQL BIDS 2008

  • Test - Windows7, SQL/BIDS 2008

I have since rewrote and simplified the query, grabbing the data into a temp table then use SQL server to rank and pare down the number of records.

Any ideas on finding the root cause of the SQL not working in the first place? And why the preview would work but running the package does not?

1
oraclessisoledbdense-rank
Out of curiosity, if you make it ORDER BY DATE_ADDED ASC) DRANK does it work? I have it in my head that the AS didn't play well – billinkc
I don't see any issue with the Oracle SQL. – Lalit Kumar B
Odd. I wonder if it's using different drivers when run as a package -- I've seen weird differences between 64- and 32-bit Oracle drivers on the same machine, one of which was used in debug, and one of which was used for release package execution. Is your project set to execute in 32-bit mode in debug? I believe a lot of people set the project setting Debugging/Run64BitRuntime to get debugging working with Oracle. – Matt Gibson

1 Answers

2
votes

I have discovered the problem. There were embedded comments using the double dash in the middle of the SQL. When I removed them, the query worked. I removed them in my original posting when I "cleaned up" the query to post it to this public forum.

This is true in both 32-bit and 64-bit mode. I also removed the AS from the AS DRANK phrase, which did not make a difference in this case. Thanks for the ideas.