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.
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.
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.
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.
To run the Lesson 2 package
In Solution Explorer, click Basic Package Lesson 2.dtsx.
On the Debug menu, click Start Debugging.
The package will run, resulting in 809 rows successfully added into the Query table in AdventureWorks.
After the package has completed running, on the Debug menu, click Stop Debugging.
To verify the contents of the ProspectiveCustomers table
On the Start menu, point to All Programs, point to Microsoft SQL Server, and click SQL ServerManagement Studio.
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.
Click Connect. SQL Server Management Studio opens.
On the toolbar, click New Query.
Type or copy the following query in the query window.
SELECT * FROM AdventureWorks.dbo.ProspectiveCustomers
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.