Using the Bulk Insert and Execute SQL Tasks

Take a time-out briefly to exercise a few of the tasks that were just discussed. First, go ahead and create a new SSIS project called Chapter3 as you saw in the last chapter. Rename the package called Package.dtsx that's created with the project to BulkLoadZip.dtsx. If you're prompted to rename the package as well, select Yes.

Before you tackle the bulk of the tutorial, go to the page for this book at www.wrox.com and download the example extract ZipCode.txt file. Place the file into a new directory called C:\SSISDemos. Then, create the following table in the AdventureWorks database in SQL Management Studio or in the tool of your choice. You will be inserting into this table momentarily.

 CREATE TABLE Chapter3 (     ZipCode CHAR(5),     State CHAR(2),     ZipName VARCHAR(16) ) 

Back in BIDS, drag the Bulk Insert task onto the Control Flow design pane. Notice that the task has a red icon on it telling you that the task hasn't been configured yet. Double-click on the task to open the editor. In the General page, type the name "Load Zip Codes" for the Name option. For the Description option, type "Loads zip codes from a flat file."

Click the Connection page. From the Connection drop-down box, select <New connection…>. This will open the Configure OLE DB Connection Manager dialog box. You're going to now create a connection to the AdventureWorks database that can be reused throughout this chapter. Click New to add a new Connection Manager. For the Server Name option, select the server that contains your AdventureWorks database. For the database, select the AdventureWorks database. Your final configuration should look like Figure 3-3, but your login information will vary based on your server's security configuration. Click OK to go back to the previous screen, and click OK again to go back to the Bulk Insert Task Editor.

Figure 3-3

You'll now see that the Connection Manager you just created has been transposed into the Connection drop-down box. For the DestinationTable option, select the [AdventureWorks].[dbo].[Chapter3] table. The other options will remain to be the default options. The RowDelimiter option will be {CR}{LF} (a carriage return) and the ColumnDelimiter option will be Tab. For the File option, you will again select <New connection…> to create a new Connection Manager. This will open the File Connection Manager Editor. For the Usage Type, select Existing File. Then point to C:\SSISDemos\ZipCode.txt for the File option. Your final screen should look something like Figure 3-4.

Figure 3-4

Next, go to the Options page and change the FirstRow option to 2. If you open the ZipCode.txt file, you'll notice that there is a header row with the column names before the data. By changing the FirstRow option to 2, you have told the task to skip the header row. Click OK to exit the editor.

With the first task configured, drag the Execute SQL task onto the design pane. Double-click on the task to open the editor. Name the task "Purge Chapter3 Table," and you can add whatever description you'd like this time. For the Connection drop-down box, select the AdventureWorks Connection Manager that you created earlier. For the SQLStatement option, type the following SQL code:

 DELETE FROM Chapter3 

Your final configuration of the General page should look like Figure 3-5. Click OK to exit the editor.

Figure 3-5

Click the task named "Purge Chapter3 Table" and drag the green arrow onto the task named "Load Zip Codes." This creates an On Success precedence constraint between the two tasks. The Bulk Insert task will not be executed unless the first task successfully executes. At this point, the package is complete. Save the package and execute it. When the package executes, the table will be purged using the Execute SQL task and then the table will be repopulated using the Bulk Insert task. A successful execution will look like what's shown in Figure 3-6.

Figure 3-6

To stop the package's execution, click the Stop button. In Chapter 5, you'll see a similar example but you'll use the Data Flow task. The disadvantage in using the Bulk Insert task is its lack of error handling and its all-or-nothing nature. If a single row fails to insert, then your task may fail (based on your setting for the maximum number of allowed errors).