DTS Subquery

[Craig0201]

Hi,

I'm trying to create a data warehouse in SQL Server using DTS to extract the data from Oracle Financials. I can't guarantee the time of day (or if) the data will be transformed. I therefore need to be able to compare the max of the last update date to the date held in Oracle to ensure I get the records.

How can I execute the subquery in DTS or do I have to do this in a different way?

Craig

 

[sguslan]

One way is to create a special table in Oracle to store the last successful run max datetime with a flag to indicate success.

As the DTS starts insert a new row in this special table with the datetime value being the current max value in the Oracle source table and set the flag to null. Use an SQL Task to do that with a connection to the Oracle server.

After a successful run set the flag to some value other than null.

Then run the query to fetch data similar to the following:

SELECT * FROM SCHEMA.ORACLETABLE O
WHERE
DATETIMECOLUMN>(
SELECT MAX(DATETIMECOLUMN) FROM SCHEMA.SPECIALTABLE WHERE
SUCCESSFLAG IS NOT NULL)