Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Transferring data from Oracle to SQL Server

Posted on 2014-02-07
6
Medium Priority
?
698 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 2000 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 2000 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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 2000 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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

718 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