Solved

Transferring data from Oracle to SQL Server

Posted on 2014-02-07
6
672 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now