Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Mar 4 '13 at 11:14

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 3 down vote accepted

SSIS does a good work of exporting data from Oracle to SQL Server. I suggest you create staging tables in SQL Server and use SSIS to extract the data for the 10 Oracle tables. Then either use stored procedures in SQL Server and execute them using SSIS or use Merge syntax and try to do both insert as well as update in Control Flow. Updates tend to be slow in Data Flow task of SSIS using Oledb command for very large number of rows.

Go through this link Using SSIS to get data out of Oracle: A big surprise! which explains the performance of SSIS while loading fairly large amount of data in a short time.

To get started for ETL process from Oracle to SQL Server try to read this documentation.

share|improve this answer
    
I agree with praveen... my vote up! –  Anoop Verma Mar 4 '13 at 16:23

I used Data Manager from IBM and it was messy. So, I wrote a simple Java program to do the migration instead and it worked like a charm.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.