IMPORT new Data since last IMPORT - DTS/Stored Procs?

Hello:

I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:

On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.

Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?

On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?

Any advice will be greatly appreciated!I wouldn't use a trigger.

You can use DTS to call stored procedures, so you can just do it all in one DTS package.

'a week ago and leave behin the OLD data' You need to filter the data coming in. You can pass variables to DTS jobs to alter the way they execute. I think you could pass in a variable to alter the where clause within the query you specify for DTS. I haven't used variables for that reason, so I 'm not sure. Anyway, schedule that with a job and pass in the appropriate date for your filter. That would get the import into SQL. Then run whatever procedures you need to clean up the data, check for validity, etc. Then call a step to export records based on whatever criteria. I'd do it all in one DTS package.

Here's a place to start
http://www.swynk.com/friends/green/DTSHowTo3.asp|||What DTS book do you recommend I acquire to learn more about passing variables to DTS and other related topics?

I also referenced the URL you gave, but I didn't believe it was too helpful in my case.

Thanks.|||I'm a google junkie; I don't have any books on DTS.

http://www.databasejournal.com/features/mssql/article.php/1461501

This link is on the page of the first link and gets a little bit more there. You have to use the same concept as in these examples.

What I would do is this:

1) create a DTS package that uses a query to pull from the oracle database. Just a canned one that has your desired date range specified so you can see what your where clause will look like.

2) save the package to a Visual Basic File. Open that file in Notepad and find the query you specified and you can see what all the object names/details are that you'll need to reference

3) Follow the examples in these two links to create an activex task in your package that will alter the query with variable that you pass into it. Actually, you don't need to pass in variables if you don't want. You could just put the logic into the activex task that determines, based on the current date, what the date range should be. It's just VBScript, so you have all the date/time functions available.

I know this is a little sketchy, but I used these examples to figure it out the first time so they do help. Swynk has some good stuff so you could search their site or join their forum also.