Transferring data from Oracle to SQL Server

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.
LVL 35
Who is Participating?
Surendra NathConnect With a Mentor Technology LeadCommented:
Linked Server
pros :
1) easy to setup
2) less development effort

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

1) Best in class for performance

1) More development is required
2) More maintenance as well licesing.
Surendra NathConnect With a Mentor Technology LeadCommented:
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.
YZlatAuthor Commented:
At this time I am transferring data from two tables, each containing about 25K rows
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Surendra NathConnect With a Mentor Technology LeadCommented:
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.
YZlatAuthor Commented:
But could you tell me what are pros and cons of one over the other?
YZlatAuthor Commented:
Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.