Solved

Transferring data from Oracle to SQL Server

Posted on 2014-02-07
6
687 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

808 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