SSIS package with BULK data load

I analyzed a C# program over the weekend for a customer that basically does the following:

• Truncate Table

• Fetch Data (source on these is either remote database or local database where job runs)

• SQLBulkCopy (.NET class, into destination table of database where job runs)

For the most part (1) and (2) are done through stored procedure calls. I believe SSIS will be more efficient in handling this process. My end result is to determine if SSIS can do a better job and then the most efficient way of designing the package.

Question(s)

In cases where there are a large number of records would SSIS get better performance with the BULK INSERT task when (2) and (3) are the same database on the same instance? Or just change the procedure call in (2) to be a INSERT INTO, taking out the need for (3)?

In cases where (2) is a remote source (another SQL instance on the same network), does SSIS handle pulling data from a remote source more efficiently than doing it through the .NET SQLClient? I would not think there is a huge difference.

As well, when (2) is a remote source that is rather large amount of data (8 million +)would it be better to have a package on the remote instance export the data; then move that file to the destination server and bulk load it?

For 8 million rows, I don't think there will be a lot of difference between SSIS and the SQLBulkCopy class. In SSIS, you do have to make sure you are using the fast load option in the OLE DB Destination, otherwise it will surely be slower.
If the source is local, you could try a SQL Server Destination, it might be a tad quicker than the OLE DB Destination.

StackExchange Database Administrators Q#33294, answer score: 4