Professional SQL Server™ 2005 Integration Services

3.4. 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 ...