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 FLOAT
s).
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:
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?
SqlBulkCopy
, which is a .NET function that can be used in a console app, Windows app, or even in SQLCLR. And regarding the SQLCLR option, I wrote such a stored procedure, DB_BulkCopy, which is available in the Free version of the SQL# SQLCLR library.