Lesson 2: Enhancing the Basic Package
New: 14 April 2006
In Lesson 1: Creating the Basic Package, you used the SQL Server Import and Export Wizard to get a quick start on a basic Integration Services package. The package has limited functionality; it only extracts data from an Excel workbook file and loads the data into the ProspectiveCustomers table of the AdventureWorks sample database.
Typically, a package also needs to manipulate and transform the data. Integration Services provides a wealth of transformations that you can use to copy, cleanse, modify, sort, and aggregate data. If you need to transform data in ways that are not supported by the standard transformations, you can easily write a script for the Script transformation or code a custom transformation to address your needs.
In this lesson you will enhance the basic package to sort the data and add a new column based on values from other columns to the dataset. In this scenario, one column contains null values, which present problems when concatenating values from existing columns. To work around this problem and generate the value for the new column, you will use a new Integration Services feature—expressions. The Integration Services expression language includes functions, operators, and type casts that you can use to build complex expressions. You will use an expression to concatenate the values from three columns and conditionally insert a space between columns, and then add the new value to the new column.
Because a new column is added to the dataset, the ProspectiveCustomers table and the OLE DB destination must be modified to include this column. You will update both the SQL statement in the Execute SQL task that created the ProspectiveCustomers table, and the OLE DB destination that writes data to the table, to include this new column. You will also map the new column in the dataset to the new column in the table.
In this lesson, you will copy and then enhance the basic created in Lesson 1. If you have not completed the previous lesson, you can also copy the completed package for Lesson 1 that is included with the tutorial.
Important
This tutorial requires the AdventureWorks sample database. For more information about how to install and deploy AdventureWorks, see Running Setup to Install AdventureWorks Sample Databases and Samples.
Lesson Tasks
This lesson contains the following tasks:
- Copying the Lesson 1 Basic Package
- Updating the Execute SQL Task
- Adding and Configuring the Sort Transformation
- Modifying the OLE DB Destination
- Testing the Lesson 2 Basic Package
Start the Lesson
Copying the Lesson 1 Basic Package
See Also
Other Resources
Integration Services Transformations
Integration Services Expression Reference