Solved

Transferring data from Oracle to SQL Server

Posted on 2014-02-07
6
690 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
  • 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
Independent Software Vendors: 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!

 
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

735 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