SSIS 并向 Oracle 发送带有日期的查询 [英] SSIS and sending query with date to Oracle

问题描述

我正在尝试创建一个流以将数据从 Oracle 表中提取到 SQL Server 表中.我正在向 Oracle 发送以下查询以获取数据:

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

当我在 Oracle 中运行查询时，返回了 505 行.当我执行 SSIS 包时，插入了 1,504 行.出于某种原因，看起来 SSIS 通过的查询没有查看 ptct 表的 where 子句 (nvl(ptct_term, ptct_end) >= to_date('01-JAN-2014')) 因为所有额外的行都是图片 (ptct) 表中没有有效条目.

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.

有人对这里发生的事情有什么建议吗?原始查询在 ptct 表上使用了一个exists子句而不是连接，但它不起作用所以我转换为连接.当存在exists子句时，会看到与上述类似的结果.

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.

推荐答案

很可能您的 NLS 设置在您的 Oracle 查询工具(sqlplus、toad...)和 SSIS 之间有所不同.尝试明确给出您的日期，如 to_date('2014-01-01','YYYY-MM-DD').

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

这篇关于SSIS 并向 Oracle 发送带有日期的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！