February 4, 2015 at 3:10 pm
I have a data flow task in an 2008 BIDS SSIS package.
The data flow task has a single OLE DB source task connected to an Oracle database and a single OLE DB Destination task connected to a SQL Server (SQL 2008 R2). There are no other tasks in the data flow. There are no errors or warnings during execution - everything shows green.
The OLE DB source task executes the query below from a variable:
select cast(p.PlanName as varchar2(200)) as Name,
cast(p.CommunityKey as varchar2(20)) as CommunityNumber,
cast(p.PlanKey as varchar2(20)) as "Number",
cast((case when p.PlanStatus = 'I' then 0 else 1 end) as number(1)) as Status,
cast(to_char(p.TriggerDate, 'MM/DD/YYYY') as varchar2(50)) as TriggerDate,
SysDate as CreatedDate
from lawsonods_plan_vw p
where p.TriggerDate >= '06-Nov-2014'
The query returns about 26,000 rows.
But when I execute the query directly from SQLDeveloper for the same Oracle database logged in as the same user, I get zero rows.
I am at a loss to understand why SSIS is getting rows for the query but a direct execution gets zero rows.
I have verified that the SSIS package and SQL Developer are connecting to the same Oracle database with the same user/password.
Any suggestions as to what else to look at for explaining why SSIS get rows?
February 4, 2015 at 6:16 pm
Did you try casting p.TriggerDate >= '06-Nov-2014'
to a date-variable? (instead of relying on strings)
->where p.TriggerDate>=TO_DATE('2014-11-06','YYYY-MM-DD')
February 5, 2015 at 9:06 am
That worked!
Thank you.
February 6, 2015 at 4:18 am
You're welcome
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply