March 17, 2006 at 1:28 pm
I have common ETL task (from Sql Server 2K to 2K5) but can not decide what is more effecient- create SSIS package or linked server/SP/job on 2K5? Thanks
March 20, 2006 at 7:17 am
If you run a query that uses a linked server, the data moves directly from the linked server to the server running the query. If you use SSIS, the data moves through the PC running the SSIS project. From a network perspective the simple answer is that the linked server is more "efficient" (unless the SSIS package runs on the target server).
But this is a very simplistic analysis that doesn't take into account all the things you can do with SSIS. If this is something more than a simple insert/select, the transformations might be done more efficiently through SSIS. The linked server query may require a distributed transaction which may have udesirable locking and/or security issues. If the linked server is a remote and/or sensitive system, there could be security considerations that make a connection from your desktop preferable to a linked server definition on the server.
If this is a small job, efficiency may not be an important issue. If it is a big job, run it both ways and compare elapsed time, CPU load, or whatever metrics you prefer to define "efficiency".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply