SSIS picks wrong date when querying oracle database on another server

49 Views Asked by At

While running SSIS etl package via SQL Server agent and filtering with TIME/date (expected date being current date minus 1) against an oracle database in another server, query returns date older than 5 months while the same query executed directly on oracle works perfectly

Below is the query

select
to_char(id) id,
to_char(packno) packno,
to_char(TIME, 'DD-MM-YYYY HH:MI:SS')TIME, 
to_char(TYPE) TYPE,
to_char(AMOUNTORIG)AMOUNTORIG,
to_char(AUTHFIID)AUTHFIID,
to_char(ORIGUNIT)ORIGUNIT
from bab.tla
where packno between (to_nchar(sysdate-2, 'YYMMDD')) || '001' and (to_nchar(sysdate-0, 'YYMMDD')) || '017'
and time 
between 
to_date(to_nchar(trunc(sysdate-1), 'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') and
to_date(to_nchar(sysdate-1, 'dd-mon-yyyy') || ' 23:59:59','dd-mon-yyyy hh24:mi:ss')
1

There are 1 best solutions below

0
On

Resolved. Issue is with the staging table accidentally changed to an old table within the flow. While the correct staging table is being truncated at the start of the work flow, the wrong table with older data is adding to the query within the flow