SSIS or LinkedServer

  • Hi

    We are using SQL Server 2005. We need to migrate Oracle data to SQL Server 2005.

    We have two options to migrtae data-

    i) Building SSIS and

    ii) Establishing a Linked Server with Oracle and writing procs.

    My query is which one of the above two will be a feasible solutiuon ?

    And what are the constraints for each approach ?

    Any help will be appreciated........

    Kind Regards

  • With SSIS you will have option to transform the data in case you need to modify it. Else you can use the import export utility to migrate data from oracle to sql. Its save you developent time.

  • If your data transfer can be a strictly scheduled process, then SSIS is preferable. Performance-wise, SSIS has some built-in optimizations (I believe) that make it superior to linked servers.

    If, on the other hand, your data connections are made through a client app GUI, then SSIS may not be a sensible choice. Instead of using a linked server though, you may want to consider using OPENROWSET to dynamically build remote connectivity at run time.

    See http://msdn2.microsoft.com/en-us/library/ms190312.aspx

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply