SSIS Data Transfer vs. Linked Server

Question

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?

Answer 1

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!