is SSIS insert bulk the same as a BULK Insert

Question

I'm doing a bulk insert of a CSV file into SQL Server 2005, using an SSIS package (not built by me)

I'm running SQL Profiler and see the insert statement as:

insert bulk [dbo].[stage_dht]( ..... )

but there's no FROM clause in that statement, so I'm curious how is it getting it's data, and is this so-called fast load the best way to bulk data into SQL?

Answer 1

Yes, the SSIS Bulk Insert task uses the same underlining functionality that the BULK INSERT command uses.

You will most likely see differences in SQL Profiler because the the Bulk Insert task will use the underlining COM object directly (which powers bulk insert), rather than simply being a GUI wrapper on the T-SQL command. So rather than specify FROM , I am guessing that it passes an open IO stream pointer to the COM object.

Bulk insert is pretty much the fastest way to load a lot of data into SQL Server because it runs in-process, "sharing the same memory address space. Because the data files are opened by a SQL Server process, data is not copied between client process and SQL Server processes".[1]

However, performance will vary if the file is on the same machine as the SQL Server or not.

[1] About Bulk Import and Bulk Export Operations