• Article

Step 6: Testing the Lesson 2 Basic Package

In this lesson, you have done the following tasks:

  • Updated the SQL statement in the Execute SQL task to include a definition for an additional column.

  • Added and configured a Sort transformation to sort the dataset by state and then by city.

  • Added a Derived Column transformation and configured it to use an expression to generate values for a new column.

  • Modified the OLE DB destination to write the new column, FullName, to the ProspectiveCustomers table.

Your package is now complete! It is time to test your package.

Important

The first time you run the package, the Drop table(s) Task will fail. This behavior is expected. The reason this happens is that the package attempts to drop and re-create the ProspectiveCustomers table; however, the first time that the package runs the table does not exist and the DROP statement fails.

Checking the Package Layout

Before you test the package, you should verify that the control and data flows in the Lesson 1 package contain the objects shown in the following diagrams.

Control Flow

Control flow for Basic Lesson 1 package

Data Flow

Updated data flow for basic lesson 2 package

Also, the package should include the following two connection managers. One connects to the customers.xls Excel workbook file and the other one connects to the AdventureWorks2008R2 database.

Connection managers for the Basic Lesson 1 package

To run the Lesson 2 package

  1. In Solution Explorer, click Basic Package Lesson 2.dtsx.

  2. On the Debug menu, click Start Debugging.

    The package will run, resulting in 809 rows successfully added into the Query table in the AdventureWorks2008R2 database.

  3. After the package has completed running, on the Debug menu, click Stop Debugging.

To verify the contents of the ProspectiveCustomers table

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server, and click SQL ServerManagement Studio.

  2. In the Connect to Server dialog, select Database Engine in the Server type list, provide the name of the server on which the AdventureWorks2008R2 database is installed in the Server name box, and select an authentication mode option. If you select SQL Server Authentication, provide a user name and a password.

  3. Click Connect. SQL Server Management Studio opens.

  4. On the toolbar, click New Query.

  5. Type or copy the following query in the query window.

    SELECT * FROM AdventureWorks2008R2.dbo.ProspectiveCustomers

  6. On the toolbar, click Execute. The Results pane shows the dataset, including the new FullName column. You can verify that your expression formatted the column value correctly depending on whether the middle initial is null.