I'm building a data mart in SQL Server which needs to extract each month from an Oracle database. Currently this is done by exporting to flat files, then running all the inserts/updates in SQL Server...
There are approximately 10 flat files to export then insert before setting off the updates. Each file contains around 3.5 million rows and 5 to 10 columns per file.
Everything now is done strictly through stored procedures and I'm looking into SSIS assuming the permissions are properly granted this week. I've also come across Rhino ETL.
Does anyone have advice on efficient though relatively painless ETL from Oracle to SQL Server?
Appreciate the help!