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?

Answer 1

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 ) )