Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I want to move data between two databases (e.g source: Oracle, destination: SQL Server), I think I have two options: Linked Server and SQL Server Integration Services. But is there any benefit using Linked Server? Is there any use of Linked Server if I have SSIS in my hand?

share|improve this question

3 Answers 3

up vote 6 down vote accepted

Linked Servers allow you to connect from SQL Server on an adhoc basis to another datasource, be it SQL Server, Oracle, or something else. Adhoc is the key word, so occasional use is fine. You'll see a lot of negative comments online about performance, hopefully Microsoft will fix in the next SQL Server after Denali.

SSIS is a more robust way of moving and transforming data, with very good exception handling. Getting some data out of Oracle should be straightforward (try the import/export wizard), but SSIS is generally considered to have a steep learning curve. On the other hand, it will make you a more valuable database professional.

It's worth mentioning replication - whilst not trivial, it's a great way of getting data around the enterprise.

share|improve this answer

Linked servers have performance overhead. If you're not updating data on the Oracle end, SSIS will be faster and likely a lot less painful.

share|improve this answer

If you want to move data then SSIS is definitely a better choice as it does a lot more. SSIS is an feature rich ETL (Extract/Transform/Load) tool and was built for moving data around. Linked servers on the other hand is more suitable for the occasional quick querying of a remote server.

With SSIS you can even save your packages and just deploy/run them the next time onwards to replay your data import/export process. Configuration files can be created to provide dynamic data while running your packages. You can even redistribute your packages if you have created them to be portable.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.