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

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?

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.