Lab E: Using the Transform Data Task
DTS Lab E: Using the Transform Data Task
In this lab, you will use the Transform Data Task to load the customer_dim
dimension. Customer_dim contains a listing of Northwind customers and is a
dimension in the sales star.
If you have trouble completing the lab, please refer to the answer file at
C:\BITechnicalTraining\DTS\AnswerFiles\Lab E customer_dim.dts.
Exercise 1
Loading Customer_dim
Open up the Lab E starter package
.1 Right-click the Data Transformation Services folder, and then click Open
Package.
.2 In the Select File dialog box, type
C:\BITechnicalTraining\DTS\Packages\Lab E.dts in the File name box,
and then click Open.
.3 In the Select Package dialog box, double-click customer_dim.
To add the Transform Data Task
.1 In the package, click the Task menu, and then click Transform Data Task.
.2 Click Customer Source as the source connection, and then click
Customer Target as the destination connection.
To configure the Transform Data Task source and destination
.1 Double-click the Transform Data Task arrow that joins the source and
target connections to open the Transform Data Task Properties dialog
box.
.2 On the Source tab, type load customer_dim in the Description box.
.3 Click the SQL query option, and then click Browse.
.4 In the Select File dialog box, type
C:\BITechnicalTraining\DTS\Common\customer_source.sql and then
click Open.
.5 Click the Destination tab, point to the Table name list, and then click the
[polaris].[dbo].[customer_dim] table.
APAC BI Technical Training
Note
Lab E: Using the Transform Data Task
To create transformations
In this procedure, create transformations to load the customer_dim dimension.
The following summary table defines the source to destination column
mappings to load the customer_dim dimension.
The steps below the table define how to implement these transformations.
Northwind source
Table
Column
Customer_dim
destination
Transformation
requirements
-----
-----
customer_dim_key
-----
Customers
CustomerID
customer_id_app
Direct Mapping
Customers
CustomerName
customer_name
Direct Mapping
Customers
BillAddress
bill_address
Direct Mapping
Customers
BillCity
bill_city
Direct Mapping
Customers
BillRegion
bill_region
Uppercase
Customers
BillPostalCode
bill_postal_code
Direct Mapping
Customers
BillCountry
bill_country
Direct Mapping
Orders
ShipAddress
ship_address
Direct Mapping
Orders
ShipCity
ship_city
Direct Mapping
Orders
ShipRegion
ship_region
Uppercase
Orders
ShipPostalCode
ship_postal_code
Direct Mapping
Orders
ShipCountry
ship_country
ActiveX
Do not include the customer_dim_key column in the data transformation
definition because it is an identity column.
.1 Click the Transformations tab, and then click Delete All to remove all
default column mappings.
.2 In the Source column list, highlight the columns in the above table that have
a Direct Mapping transformation requirement.
.3 In the Destination column list, highlight the destination columns in the
above table that have a Direct Mapping transformation requirement.
.4 Click New, click Copy Column, and then click OK.
.5 Type copy column transformation in the Name box.
.6 Click Properties and verify the source to destination column mappings by
using the table above. Reorder if necessary. When finished, click OK, and
then click OK to exit out of the Transformation Options dialog box.
To create an Uppercase transformation
.1 In the Source column list, highlight the BillRegion and ShipRegion
columns.
.2 In the Destination column list, highlight the bill_region and ship_region
destination columns.
.3 Click New, click Uppercase String, and then click OK.
.4 Type uppercase string transformation in the Name box.
APAC BI Technical Training
Lab E: Using the Transform Data Task
.5 Click Properties and verify the source to destination column mappings.
When finished, click OK, and then click OK to exit out of the
Transformation Options dialog box.
To create an ActiveX Script transformation
.1 In the Source column list, highlight the ShipCountry source column.
.2 In the Destination column list, highlight the ship_country destination
column.
.3 Click New, click ActiveX Script, and then click OK.
.4 Type activex script transformation in the Name box.
.5 Click Properties to open the ActiveX Script Transformation Properties
dialog box.
.6 Click Browse, change the Files of type to All Files, browse to the
C:\BITechnicalTraining\DTS\Common\customer_activex.txt file, and
then click Open.
.7 Click Test, click OK, and then click View Results. Notice one of the
United Kingdom values, click OK, and then click Done.
.8 Click OK, and then click OK to exit out of the Transformation Options
dialog box.
To define the package workflow
.1 Click the Delete Execute SQL Task, press the CTRL key, and then click the
Customer Source connection.
.2 Click the Workflow menu and then click On Success.
To save the DTS package
.1 On the Package menu, click Save As.
.2 Use the information in the following table to save the DTS package to your
local SQL Server. Accept the defaults for any options that are not listed, and
then click OK.
Parameter
Value
Package name
customer_dim
Location
SQL Server
To execute the DTS package
.1 Click the Execute play button on the toolbar, or click the Package menu,
and then click Execute.
.2 Click OK, click Done, and then close DTS Package Designer.
To verify package results
.1 Open Query Analyzer.
.2 In the Connect to SQL Server dialog box, click OK.
.3 Go to the database list in the toolbar and click polaris.
.4 Type the following query:
APAC BI Technical Training
Lab E: Using the Transform Data Task
4
SELECT * FROM customer_dim
.5 Click the Execute Query play button on the toolbar.
.6 Review data results. You should have 89 records in the customer_dim
dimension. Verify one record of data by using the following table.
Column name
Value
customer_dim_key
<Values may vary>
customer_id_app
ALFKI
customer_name
Alfreds Futterkiste
bill_address
Obere Str. 57
bill_city
Berlin
bill_region
NULL
bill_postal_code
12209
bill_country
Germany
ship_address
Obere Str. 57
ship_city
Berlin
ship_region
NULL
ship_postal_code
12209
ship_country
Germany
APAC BI Technical Training
