Calculated Columns Package Sample

The Calculated Columns sample is a package that processes archived sales transactions. The package uses an OLE DB source, two Derived Column transformations, a Sort transformation, and an Aggregate transformation. The results are sent to a flat file by using a Flat File destination.

The Calculated Columns sample package illustrates how to extract data from a table, perform mathematical operations on data, aggregate data values, sort data, and write data to a file. The sample package also uses a package configuration, a variable, and a property expression to dynamically update the location of the file to which the sample writes and copy the file to that location.

If you run the sample on a non-English version of Windows, you may need to substitute the localized name of the Program Files folder to open or run the sample.

Important

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

• The sample package and data files that it uses must be installed on the local hard disk drive.
• You must have installed and have administrative permissions on the AdventureWorks database. This sample retrieves data from the TransactionHistoryArchive table of the sample AdventureWorks database. By default, this table is installed with the AdventureWorks database.
• If you intend only to run the sample package from the command line, you must install SQL Server 2005 Integration Services (SSIS).
• If you intend to open the package in SSIS Designer and run the sample package, you must install Business Intelligence Development Studio.

For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server Books Online. To obtain the latest version of the samples, including new samples released since the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Location of the Sample Package

If the samples were installed to the default installation location, the Calculated Columns sample package is located in the following folder:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\.

The following files are required to run this sample package.

File	Description
CalculatedColumns.dtsx	The sample package.
results.txt	Flat file sample data.

Running the Sample

The package can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.

If you are using a non-English version of Windows, you may need to update the ConnectionString property of any file connection managers used in the package to run the sample package successfully. Please verify that the path used in the connection manager is valid on your computer, and if required, modify the path to use the localized name of the Program Files folder.

For this sample, you may have to update "Program Files" in the ConnectionString property for the results.txt connection manager.

To run the package by using dtexec

1. Open a Command Prompt window.

2. Use the Change Directory command, cd, to change the directory to C:\Program Files\Microsoft SQL Server\90\DTS\Binn, the location of dtexec.

3. Type the following command:

   dtexec /f "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
   

4. Press Enter.

For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server 2005 Books Online.

To run the package in Business Intelligence Development Studio

1. Open Business Intelligence Development Studio.

2. On the File menu, point to Open, and then click Project/Solution.

3. Locate the CalculatedColumns Sample folder, and then double-click the file named CalculatedColumns.sln.

4. In Solution Explorer, right-click CalculatedColumns.dtsx in the SSIS Packages folder, and then click Execute Package.

Components in Sample

The following table lists the tasks, containers, data sources and destinations, and transformations that are used within the sample.

Element	Purpose
Data Flow task	The Data Flow task, Calculate Values, executes the data flow in the package.
File System task	The File System task, Copy File, copies results.txt from the installation folder in Program Files, a folder to which users typically do not have write access, to the user's Temp folder.
OLE DB source	The first component within the data flow is an OLE DB source named Extract Data. This loads the archived sales transaction source data from the TransactionHistoryArchive table in the AdventureWorks database.
Derived Column transformation	The first Derived Column transformation is named Calculate LineItemTotalCost. This transformation creates a new column, LineItemTotalCost, by multiplying the quantity by the cost for each sales transaction and storing it in the new column. This new column is then added to each output row.
Aggregate transformation	The Aggregate transformation, Sum Quantity and LineItemTotalCost, groups the data by the ProductID column, and for each ProductID, calculates the sum of its Quantity column as the QuantitySum column, and calculates the sum of the LineItemTotalCost column as the TotalCostByID column.
Derived Column transformation	The second Derived Column transformation, Calculate Average Cost, adds a new column, AvgCostByID, to each output row. The column contains the TotalCostByID divided by the QuantitySum for each ProductID.
Sort transformation	The Sort transformation, Sort by ProductID, sorts the results by the ProductID column.
Flat File destination	The Flat File destination, Load Data, saves the data to the flat file, results.txt.
File connection manager	The three File connection managers connect to the source, the copy of the result.txt file, and the Temp directory.
OLE DB connection manager	The OLE DB connection manager, (local).AdventureWorks, connects to the AdventureWorks database on the local server.

Sample Results

The execution results of the Calculated Columns sample package is saved to a text file located in the temp directory. To locate the temp directory on your computer, click Start, click Run, type %temp%, and then click OK. This will open your temp folder. In this folder, find and open results.txt. The contents of this file should match the contents of the results.txt file found in the folder C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns. This pre-populated results.txt file is included for you to print or view before running the package.