Lesson 2: Enhancing the Basic Package

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