SSIS Data Transfer vs. Linked Server I'm currently designing a SSIS package, which will do following:

Clear unwanted records in destination
Do ETL using stored procedures and put everything into a table prepared for export in source
Inserts data from source to destination

So the final step is pretty much to transfer whole table data from Source into Destination and I've got two options now:

SSIS Data Flow
Linked Server

Ideally I'd like to use Data Flow task in SSIS, but looking at performance comparison, it looks like Linked Server wins. I'm doing it that way:
In SSIS I do the following

Run a select statement using OLE DB Source
Insert into destination using OLE DB Destination
Takes about 4 seconds when deployed, about 10 seconds on my machine


Using Linked Server I do the following

Insert records into #Table querying remote server
Insert into actual table
Takes about 2-4 seconds

Here's query I'd use:
SELECT *
INTO #Table
FROM RemoteServer.MyDB.dbo.MyTable;

INSERT INTO dbo.MyTable
SELECT *
FROM #Table AS T;

I'm testing with relatively small data set, just about 14k rows (75 columns in total, most of them are FLOATs).
Both servers are under the same network.
I've configured connection managers in SSIS to use Microsoft OLE DB Provider for SQL Server with Packet Size of 32767 as suggested here:
https://gqbi.wordpress.com/2013/06/19/ssis-fastest-data-flow-task-item-for-transferring-data-over-the-network/
Am I doing something wrong, because I'd expect SSIS to run faster than Linked Server?
Is there another way to speed performance up in SSIS package and win against Linked Server?
hi Linked servers in general not best practice.  I generally recommend against them.  To many reasons to explain here....this would be come a white paper.  hahahah  I have been doing ETL and data security for 25 years.  please consult an architect and don't ask people on internet claiming to be architect.  there are very few people out there with my level of experience.  there is never one answer either...it depends on many factors how you approach this. AND testing small amount of records is not valid test.  do more intense testing.   best of luck!