Forum Discussion
Using SSIS Expression to build query to run on Oracle source
I am trying to build a query to run on oracle source by putting it in a variable, I am pulling max of date into a variable varChildpkgMaxDt(e.g. 2024-08-10 17:21:04.670 ) from SQL server target table.
I need to build a query to run on oracle source. I have used datetime as datatype for varChildpkgMaxDt to fetch from sqlserver in parent pkg and passing this as a parameter to child pkg.
I created a varCpkgQuery variable as string and coded as below, the out put I am getting date, will this be the right way to execute this query in oracle I am using oledb as source for oracle
I also want to ensure that the varChildpkgMaxDt is properly compared in oracle with NVL(REVIEWDATE,CREATED) as this two fields in oracle are date with time stamp up till 3mill sec (2024-08-10 17:21:04.670)
"SELECT *
FROM dbo.virDocument
WHERE NVL(REVIEWDATE,CREATED) > '" + (DT_WSTR, 50)(DT_DBTIMESTAMP2,3) @[User::varChildPkgMaxDt] + "'"
Was able to remove the err, and now with above change I am getting '2024-08-11 21:00:43.000' do I have to use to_date or to_char for oracle to consider this as date and compare it ?
Using oracle 19 C source, VS 2017 SSDT & SQL Server 2016