DTS - How to...

I am new to DTS and I need some help. I have created a package which connects to an Oracle db, runs a query and then either inserts or updates a sql server table based on a DTSLookup. The package contains 3 connections and one DataDrivenQueryTask. It eventually will be scheduled to execute every 10 minutes.

Its working at the moment but there is one part I am missing. The table in Oracle has a field which contains the date the record was last modified. I have currently hardcoded into the where clause this statement LastModDate > trunc(sysdate).

This returns all records modified for the current day but of course, everytime it executes it will return records I have already processed.

Sorry, for babbling on but finally here's the question

How can I use a variable in my Source query? I was hoping I could store the last execution date/time in a sql table, retrieve it and use it in in the Where clause. Something like LastModDate > @.LastExecuteDate. I am not sure how to set this up. Any ideas or pointers you have would be great?One approach:

Write an ActiveX Script to take the place of the Connection to Oracle. In your ActiveX Script, establish the connection to Oracle, pull the data into SQL in a temp table and then use the Data Driven Query Task to process the data from the temp table. The ActiveX script can contain either a lookup to a date/time field which us updated each time the script processes, or you can take the max date/time field from the last job processed as your new starting point.

There are lots of things wrong with this approach, but it is doable.

Another Approach:
In the ActiveX script for the Data Driven Query task, you can do a similar thing with pulling in the last date/time stamp and then use the DTSTransformStat_SkipRow to skip over a record that you don't want to process. This still involves pulling the data across the nework from Oracle to SQL, but you will save some processing time.

Again, there are porbably lots of things wrong with this approach, but it is doable.

Finally, I have not yet discovered a way to manipulate the SQL text in a Data Pump or Data Driven Query object. I believe that it is theoretically possible to do it (by manipulating the DTS package in SQL DMO), but I have never really had the time to fully explore this.

Best of luck to you...

Hugh Scott

Originally posted by brucevde
I am new to DTS and I need some help. I have created a package which connects to an Oracle db, runs a query and then either inserts or updates a sql server table based on a DTSLookup. The package contains 3 connections and one DataDrivenQueryTask. It eventually will be scheduled to execute every 10 minutes.

Its working at the moment but there is one part I am missing. The table in Oracle has a field which contains the date the record was last modified. I have currently hardcoded into the where clause this statement LastModDate > trunc(sysdate).

This returns all records modified for the current day but of course, everytime it executes it will return records I have already processed.

Sorry, for babbling on but finally here's the question

How can I use a variable in my Source query? I was hoping I could store the last execution date/time in a sql table, retrieve it and use it in in the Where clause. Something like LastModDate > @.LastExecuteDate. I am not sure how to set this up. Any ideas or pointers you have would be great?