The Bulk Insert Task

Aside from the multiphase data pump, the other key DTS mechanism for loading data is the Bulk Insert task. The Bulk Insert task basically exposes the functionality of the T-SQL BULK INSERT command via a graphical interface and internally calls it to load data into the server. The fact that BULK INSERT is the actual mechanism used to load data into the server brings with it a couple of important considerations. First, the path to the source file must be specified in relation to the target SQL Server. So, if you're using a Bulk Insert task to copy a file from a client machine to a remote SQL Server, you must specify a UNC path to the file, and the account under which SQL Server is running must have access to the file (e.g., LocalSystem won't work because LocalSystem has no network rights). Second, you'll see much better performance if the file you're loading resides on the same machine as the SQL Server instance. Internally, the T-SQL BULK INSERT command is implemented by a COM object that runs inside SQL Server. If the file being loaded resides on the SQL Server machine, the COM object simply opens it and reads it as a local file. If the file resides elsewhere on the network, you may find that network bandwidth limitations throttle the speed of your bulk load, just as they would with any other bulk load facility such as bcp.exe or the BULK INSERT command itself.

Note that you cannot use a Bulk Insert task to load data directly from one SQL Server database table into another. The source for a Bulk Insert task must be an operating system file. That said, you can enable high-speed bulk copy processing with normal Transform Data tasks via the task option Use fast load (which is enabled by default).

Also note that Bulk Insert tasks do not support the types of ActiveX script transformations supported by the more ubiquitous Transform Data task. Because Bulk Insert calls the T-SQL BULK INSERT command, its functionality is more limited than that of the Transform Data task.

You can load the sample package BulkInsertExample.DTS from the CH20 folder on the CD accompanying this book to experiment with Bulk Insert tasks. I encourage you to start a Profiler trace before executing the package so that you can see the T-SQL code being sent to the server.