I have an opinion question for you. Multiple opinions welcome! My site is preparing for a new control system to be deployed later this year. This system uses an Oracle 11g database. This database houses lots and lots of data that we on the business side need for analysis and reporting. (We're talking probably over a million rows of data across 10 or so tables per day.) Accessing the production database directly is not allowed. I am currently writing requirements for getting a daily extract of the data we need, so I can insert it into my reporting database, which is SQL Server 2008 R2. Both databases exist on the same network.
In the current environment I have direct access to the production Oracle 9i database. I run a query to pull the data I need from a particular table, then transform that data into simple XML, then pass that XML to a stored procedure on my SQL Server that parses it into tabular form, and inserts that data into the appropriate table. This whole process takes several hours.
My question is this: if you were able to write the requirements for this new model that's coming, how would you design the transfer to happen? I don't know Oracle database capabilities very well, and my knowledge of SQL Server, while growing, isn't complete yet... I don't know really anything about SSIS, but am open to solutions that would incorporate this.
Clarification: I don't need super detailed answers here... just give me the general route you would go, and why you feel it's the most appropriate. :-)
Thanks for your opinions!