Oracle OLEDB Source, not recognising (+) join syntax, decode, to_char. 1

[LFCfan]

Hi all

I'm trying to import some data into Sql Server 2005 from Oracle 10g, using a Sql Command which has the (+) outer join syntax as below

from  s,  c, d, o
where s.KEYVAL = c.MDKEYVAL
and s.SRCLOSEACT = d.CODEVALUE(+)
and d.LISTNAME(+) = 'SRCLOSEACT'

and also various standard Oracle functions like to_char, nvl, and decode.

SSIS has trouble parsing the query, stumbling on each of those functions and the (+) syntax.

We're getting around this now, but my question is:
Is only ANSI syntax "allowed" in these OLEDB Sources?

Many thanks

~LFCfan

 

[MDXer]

The oracle functions should work. I have a number of places where I am using Oracle based functions like TO_Date and TO_Char and have no issue. as for the Syntax I would guess that yes it needs to be ansi compliant syntax so you need to do

From TableA a
    Left Outer Join TableB b ON a.Column1 = b.Column1

or whatever your query needs to look like. You are best off building your queries directly against Oracle and then putting the functioning query in SSIS.

I can tell you that Oracle does not allow the use of Paramterized Queries in SSIS. There are work arounds to do it.

 

[LFCfan]

Thanks MDXer

Turns out it was my own stupidity entirely - I seem to have a touch of the Fridays and was trying to run the Oracle query against the Sql Server connection!

to_char etc working fine, and (+) notation also.

Apologies for wasting your time!

~LFCfan

 

[MDXer]

np I hope the rest of my day goes as easy.