Forum Discussion

Neils2401's avatar
Neils2401
Copper Contributor
Aug 12, 2024

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

No RepliesBe the first to reply

Resources