Load data from sql server(source) to oracle (target)

Question

0.00/5 (No votes)

See more:

Hi,

I was asked to create new tables in Oracle and load data in to it from MS Sql Server. I created the tables in oracle using star schema with fact and dimension tables and I created sequences for unique id columns in dimension table and triggers to assign the sequences to that particular column. Here is my big problem as I am new to this data warehouse part I never done loading data from one data base (sql server ) to other data base (oracle ), some one told that bulk loader will be best option and some said SSIS package will be best. Which one is best and which will load data fast please let me know, and please give me some instructions or links of instructions to do these particular process as I was new to this concept.

Thanks in advance

Posted 11-Jan-11 5:37am

raju9789

Updated 11-Jan-11 6:05am

Henry Minute

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*dan!sh* · Accepted Answer · 2011-01-11T09:29:00

Solution 1

You can use SSIS for this purpose. Although using MS Oracle provider might turn out to be slow. To take care of that, you can use this[^] from MS.

If you are totally new to SSIS, this[^] should be a good start.

Posted 11-Jan-11 9:29am

dan!sh

Espen Harlinn · Accepted Answer · 2011-01-11T09:48:00

Oracle ODAC supports bulk loading, you can download it from here:
Oracle .NET Developer Center[^]

Bulk loading outperforms data inserts using sql from .Net.

You can also Use the CREATE DATABASE LINK[^] statement to create a database link to the SQL Server.

Or try Sql Loader Utility[^]

If the database isn't huge - choose what you are comfortable with ...

Bulk load using ODAC is fairly fast

Regards
Espen Harlinn