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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.
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.