I’ve been doing quite a bit of SSIS 2005 lately and I’ve come across a weird problem that almost had the best of me. It’s no secret that I absolutely *hate* Oracle and this problem I just had didn’t make it any better.
What I had was a quite long and complex pl/sql query (over 250 lines of code!) that was running smoothly in sql developer, the Oracle OLE DB provider in SSIS had absolutely no problems with the code when previewing data or mapping the column metadata. But when I saved the package and ran it I got the error message saying “ORA-000938: missing expression”. Sooo…what to do? Obiously there was some kind of syntax error but I have a query that runs perfecty in SQL Developer, it validates without a hitch in SSIS and I can even preview the data in the OLE DB source object in SSIS. Quite amazing to be honest!
So, after 7 hours (!! I kid you not!) of debugging this query I had a colleague step in to my office for a few minutes. And then he dropped the bomb: “Did you remove all the comments in your query??”. I just stared back at him in disbelief but at that point I was ready to do just about anything so I tried it and; Voila. The query ran through. After a bit more debugging I learned that it was the “–” (double dash) it didn’t like and that the /* */ works just fine and I think it has something to do with th driver actually removing all the carriage returns in the statement so anything after the first comment is also commented out. Go Oracle!
Don’t ever comment your PL/SQL queries in ssis using “–” (double dash), it simply will not work!