SSIS issue when communicating with Oracle (T-SQL vs. PL/SQL?)

Question

I have an SSIS package set up to pull data from an Oracle database into SQL Server. I recently ran into an issue that was preventing this data pull.

The following code works fine in Oracle SQL Developer (it returns rows, as it should):

SELECT a.MyField  ,
    a.MyOtherField,
    a.FromDate
FROM MyTable a
WHERE a.FromDate BETWEEN CONCAT('01-', TO_CHAR(ADD_MONTHS(SYSDATE, -13), 'MON-YY')) AND TO_CHAR(LAST_DAY(SYSDATE), 'DD-MON-YY')

However, when using this as the SQL command text of an OLE DB Source component in SSIS, it returns no records.

I'm not sure if this is an SSIS issue or a difference in language syntax (I believe this is due to the different systems' date syntax, but I do not have a strong enough grasp of PL/SQL to know how to correct this.).

Any ideas?

Justin Cave · Accepted Answer · 2010-10-06 17:58:55Z

2

Most likely, the problem is that you are comparing a date to a string. This forces Oracle to do an implicit cast using the session's NLS_DATE_FORMAT. Since this is session-specific, it is fragile as different clients will end up doing the conversion differently.

You are almost certainly better off rewriting the query in order to compare a date to other dates, i.e.

SELECT a.MyField  ,
       a.MyOtherField,
       a.FromDate
  FROM MyTable a
 WHERE a.FromDate BETWEEN trunc( add_months( sysdate, -13 ), 'MM' ) AND
                          trunc( last_day( sysdate ) )

answered Oct 6, 2010 at 17:58

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSIS issue when communicating with Oracle (T-SQL vs. PL/SQL?)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related