SSIS Data flow vs Custom transfer scripts vs Bulk Insert

Question

When copying the results of a view from a database on one server, to a another database on a new server (both running SQL Server 2008), which of the following methods is the likely to be the most efficient?

1. SSIS Dataflow task with OLE DB Source/Destination

Ignore errors, just set up for demonstration purposes

2. Custom scripts

e.g.

using (SqlConnection connection = new SqlConnection(sourceConnectionString))
using (SqlCommand command = new SqlCommand(sourceQuery, connection))
{
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnectionString))
        {
            bulkCopy.DestinationTableName = destinationTable;
            //Any Column mapping required
            bulkCopy.WriteToServer(reader);
        }
    }
}

3. SSIS Bulk Insert

I don't see how this would be any different to using custom scripts, but without the added inflexibility that it only works with tables/views and not stored procedures.

I realise this is not quite like for like comparison because there are additional levels of logging and error handling etc available with the various options, but lets assume I require no logging at all to try and make the playing field as even as possible.

What does "most efficient" mean? Shortest development time? Fastest execution? Fewest lines of code? Lowest CPU, RAM or I/O usage? Easiest to debug? Easiest to maintain? — Pondlife, Jul 09 '12 at 16:10
Sorry, I meant the efficiency of execution (fastest time and lowest CPU, RAM & and IO use). — GarethD, Jul 09 '12 at 16:16

score 3 · Accepted Answer · edited May 23 '17 at 10:32

3

According to this SO Post, BULK INSERT performs better than SqlBulkCopy. However, according to this post, the DataFlow task is preferred over the Bulk Insert SSIS task

edited May 23 '17 at 10:32

Community

1
1

answered Jul 09 '12 at 16:53

ssis_ssiSucks

1,476
1
12
11

score 3 · Answer 2 · answered Jul 09 '12 at 20:34

This isn't really answering your question directly, but...

The short answer to your question is probably to try different approaches using a representative data set and see for yourself. That will give you an answer that is more meaningful for your environment than anyone on SO can provide, as well as being a good way to understand the effort and issues involved in each option.

The longer answer is that the SSIS Bulk Insert task is most likely the slowest, since it can only load flat file data. In order to use it, you would have to export the data to a file on the source server before reloading it on the target server. That could be useful if you have a very slow WAN connection between the servers, because you can compress the file before copying it across to minimize the data volume, otherwise it just adds more work to do.

As for the difference between SSIS and SqlBulkCopy I have no personal experience with SqlBulkCopy so I can only suggest that if there is no clear 'winner' you go with the simplest, easiest to maintain implementation first and don't worry about finding the fastest possible solution until you actually need to. Code only needs to run fast enough, not as fast as possible, and it isn't clear from your question that you actually have a performance problem.

SSIS Data flow vs Custom transfer scripts vs Bulk Insert

2 Answers2