Professional SQL Server™ 2005 Integration Services

3.3. Bulk Insert Task

The Bulk Insert task allows you to insert data from a text file (also called a flat file) into a SQL Server in the same manner as a BULK INSERT statement or the bcp.exe command-line tool. This task is in the controller flow only, and it does not generate a data flow. A disadvantage to the task is that it does not allow any transformations to occur to the data in flight. In exchange for this, you have the fastest way to load a lot of data into a SQL Server since it's done in a bulk fashion.

When you create a Bulk Insert task and go to configure it, the Bulk Insert Task Editor will open (shown in Figure 3-2). As in most tasks, the General page allows you to name and describe the task. Make sure you name it something that describes its unit of work, like "Prepare Staging." This will help you later when you deploy the package and troubleshoot problems.

The most important page, which is shown in Figure 3-2, is the Connection page. This page lets you specify the source and destination for the data. Select the destination from the Connection drop-down box in the Destination Connection group. Next, specify a destination table from the next drop-down box below the destination connection. While you're specifying connections, also specify the source connection's file name in the File drop-down box. Both the source and destination connections use the Connection Manager. If you haven't already created the shared connections, you'll need to create a new one from the source ...