Oracle CTE to SQL Server Using SSIS OLE DB Source
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:



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.