• Article

Task 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 AdventureWorks 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 AdventureWorks.

  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 AdventureWorks 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 AdventureWorks.dbo.ProspectiveCustomers

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

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.