We are looking at setting up a SQL report server which automates the task of importing Oracle tables. However it seems to be an unknown how to reliablily and automatically import tables from Oracle into SQL. The total size is 500Meg now but will probably grow to a couple of gigs before long. Importing needs to not take too long and would be good if people could still query the data while the update is occuring. So I believe we need to append tables only.
Well, the basic approach would be to create a set of stored procedures which would append the SQL tables from the Oracle tables based on simple outer joins. They could be kicked off periodically as a SQL Server Agent job.
DTS (Data Transformation Services) allows you do do the same thing, but gives you a nice GUI for configuring everything. You add a server connection for the Oracle server, a server connection for your SQL Server, and define individual transformation steps for each of your tables. You can customize the source SQL to include only new rows (again, outer joins based on table keys), and transform the data along the way if youd like. Again, the DTS package can be kicked off on a schedule by SQL Server Agent.
I haven't done a lot with DB Replication, but I think it's pretty much SQL to SQL, so I'm not sure how much that would help. The first two approaches would give you everything you are looking for, I would think.
I use the first approach to warehouse data from nine Informix servers every 5 minutes. Works pretty well.
Well ... the best approach would be to create a linked server and a repository of the tables you will need to transfer. Then you can write a stored procedure to dynamically make queries to transfer the data from oracle to SQL server.