1

I am trying to create a flow to pull data from an Oracle table into a SQL Server table. I am sending the following query to Oracle to get the data:

select distinct CHLD.id,
       nvl(chld_c_spl, 'N'),
       to_char(chld_d_start, 'YYYY-MM-DD') chld_d_start,
       to_char(chld_d_end, 'YYYY-MM-DD') chld_d_end
  from child chld, 
       picture ptct
 where CHLD.id = PTCT.chld_id
   and nvl(chld_d_end, sysdate) >= to_date('01-JAN-2014')
   and chld_c_veri in ('HC','DR')
   and nvl(ptct_term, ptct_end) >= to_date('01-JAN-2014')

When I run the query in Oracle, I get 505 rows returned. When I execute the SSIS package, 1,504 rows are inserted. For some reason, it looks like the query SSIS passes is not looking at the where clause for the ptct table (nvl(ptct_term, ptct_end) >= to_date('01-JAN-2014')) since all the extra rows are rows that do not have a valid entry in the picture (ptct) table.

Does anyone have advice as to what is going on here? The original query used an exists clause on the ptct table instead of the join, but it didn't work so I converted to the join. When the exists clause was there, similar results were seen to those described above.

scottr
  • 65
  • 1
  • 6
  • Can you expand a bit on your SSIS package? When you send the query to oracle, are you doing so through an Execute SQL Task, setting a specific connection string to your Oracle DB? Or are you using an OLEDB source? Do you send the resultset through or query a staging area when executing your package? – Phoenix Feb 26 '15 at 00:04
  • I am using a Data Flow Task with an OLE DB Source using the Oracle connection manager. The Oracle connection manager is using the Oracle Provider for OLE DB with the db being defined in the tnsnames.ora file. Data access mode = SQL Command and the SQL command text = the query above. I am then just sending it to a ADO NET Destination that is the SQL Server connection directly into a table with the 4 columns in the query. – scottr Feb 26 '15 at 00:07

1 Answers1

1

Most probably your NLS settings are different between your Oracle query tool (sqlplus, toad...) and SSIS. Try to give your date explicitly, as to_date('2014-01-01','YYYY-MM-DD').

select distinct CHLD.id,
       nvl(chld_c_spl, 'N'),
       to_char(chld_d_start, 'YYYY-MM-DD') chld_d_start,
       to_char(chld_d_end, 'YYYY-MM-DD') chld_d_end
  from child chld, 
       picture ptct
 where CHLD.id = PTCT.chld_id
   and nvl(chld_d_end, sysdate) >= to_date('2014-01-01','YYYY-MM-DD')
   and chld_c_veri in ('HC','DR')
   and nvl(ptct_term, ptct_end) >= to_date('2014-01-01','YYYY-MM-DD')
Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69
  • Awesome! Thanks Atilla, that worked. I will accept your answer in a couple minutes when it allows me to. – scottr Feb 26 '15 at 00:10