I am working on a proof of concept, and basically trying to import multiple tables of data from an Oracle 8i server to SQL 2000.
The initial import is of all rows in about 7 tables. However, from then on, the business rule would be to import only changed and added rows, let's say every hour. The Oracle tables all have a last modified time on them, so that a query can determine whether the row is changed or added when compared to meta data stored in SQL 2000 if needed.
1) What is the best way to do the first import. Currently, I'm thinking DTS?
2) What is the best way of doing the changed/updated row import? So far, I've tried heterogeneous queries, but I'm running into data type problems (meaning that SQL 2000 doesn't recognize some of the data types in the Oracle tables.
Any help here would be greatly appreciated. I've done work with SQL 2000 before, but not much with importing data.
I would use DTS for that task, Microsoft OLE DB Provider for Oracle - Microsoft OLE DB Provider for SQL Server connections with Transform Data Task where you can run SQL query against Provider for Oracle (in Oracle SQL dialect). I think no problem with that. martin