The project I'm on now might have a requirement to keep a copy of data in an Oracle database in a Sql Server database. We need to keep the Sql Server database up to date with changes from the Oracle database, and obviously seek some sort of transactional updates instead of dumping an entire table/schema/whatever.

I don't have much experience with Sql Server, but digging around, I found that Sql Server has a feature called Data Transformation Services, which should do what we need. I'm searching for what reading material I can find, but does anyone have experience with this type of data transfer?

Can I do some sort of incremental exports with DTS or am I limited to queries/full tables? If I'm limited to queries, can I do something to query fields by date? Eg, query on date1, next time DTS runs query anything added after date1 and so on? Maybe a stored procedure on the Sql Server side.

I believe we will seek daily updates, and there should be very few new records in the Oracle database so we're not talking about large transfers.

If at all possible I'd like to avoid anything in the Oracle database(stored procedures, etc), although exports or flatfile dumps might be a possibility.

Is DTS the right way to go or is there a better option?

Thanks