gotchasqlMinor
What's the difference between Linked Server solution and SSIS solution?
Viewed 0 times
thewhatssisanddifferencebetweenserversolutionlinked
Problem
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#5712, answer score: 9
Revisions (0)
No revisions yet.