Solved

Transferring data from Oracle to SQL Server

Posted on 2014-02-07
6
693 Views
Last Modified: 2016-02-10
I need to export some data out of Oracle database 11g (AIX OS), to SQL Server database on a windows server. THis needs to be automated and done on daily basis.

I have two options:

1) Create a linked server
2) use SSIS package

I am leaning more towards building SSIS package. Can anyone give me pros and cons of using one over another.
0
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 500 total points
ID: 39842161
it really depends...

if you data is a small one like 1000 - 10,000 rows, then I would suggest go with a linked server
A simple stored procedure along with the SQL Agent job will serve the purpose. SSIS will be a huge overload for this....


If the number of rows are well over than 50,000 then go with the SSIS as it will do well in that place.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39842281
At this time I am transferring data from two tables, each containing about 25K rows
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 500 total points
ID: 39842354
Ok, then you can simply go for linked server rather than employing the SSIS to do this task...

Unless you have a data conversion that needs to be done some where in between.
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 35

Author Comment

by:YZlat
ID: 39847634
But could you tell me what are pros and cons of one over the other?
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39847718
Linked Server
pros :
1) easy to setup
2) less development effort

Cons:
1) when data is huge there will be performance problem.

SSIS Task
Pros:
1) Best in class for performance

Cons:
1) More development is required
2) More maintenance as well licesing.
0
 
LVL 35

Author Closing Comment

by:YZlat
ID: 39851501
Thank you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question