Lab A: Creating a Simple Data Load
DTS Lab A: Creating a Simple Data Load
In this lab, you will load the product_dim dimension of the polaris data
warehouse with data copied from a subset of the Northwind OLTP Categories
and Products tables.
If you have trouble completing the lab, please refer to the answer file at
C:\BITechnicalTraining\DTS\AnswerFiles\Lab A product_dim.dts.
Exercise 1
Getting Started
In this exercise, you will start a new DTS package and create source and
destination connections to SQL Server.
To start Enterprise Manager and launch the DTS Designer
.1 Click Start, point to Programs, point to Microsoft SQL Server, and then
click Enterprise Manager.
.2 In the console tree, double-click Microsoft SQL Servers.
.3 Double-click SQL Server Group to list the servers belonging to the
SQL Server Group.
.4 Double-click the desired server name to establish a connection to
SQL Server. After a connection is established, a folder list appears.
.5 Double-click the Data Transformation Services folder to list its contents.
.6 Now right-click on Data Transformation Services and select New
Package
To set up the Product Source NW connection
.1 In the new package, click the Microsoft OLE DB Provider for SQL
Server icon in the Connection toolbar.
.2 In the New connection box, type Product Source NW as the connection
name.
.3 In the Database drop down, select Northwind.
.4 Click OK.
To set up the Product Target NW connection
.1 On the Connection toolbar, click the Microsoft OLE DB Provider for
SQL Server icon.
.2 In the New connection box, type Product Target NW as the connection
name.
.3 In the Database drop down, select polaris.
.4 Click OK.
APAC BI Technical Training
Lab A: Creating a Simple Data Load
Exercise 2
Adding a Transform Data Task
In this exercise, you will create a Transform Data Task to load data from
Northwind product and category tables into the product_dim dimension.
Warning: Do not load data into the product_dim_key column.
To add a Transform Data Task
.1 In the new package that you have created, click a blank area of the design
sheet.
.2 Click the Transform Data Task arrow icon in the Task toolbar.
.3 Click the Product Source NW connection as the source connection, and
then click the Product Target NW connection as the destination
connection.
To configure the source and destination of the Transform Data Task
.1 Double-click the Transform Data Task arrow that joins the Product
Source NW and Product Target NW connections.
.2 Click the Source tab and type load Northwind data in the Description
box.
.3 Click the SQL query radio button, click Browse, browse to the following
file, and then click Open:
C:\BITechnicalTraining\DTS\Common\product.sql
.4 Click the Destination tab, and then click [polaris].[dbo].[product_dim]
from the Table name list.
To copy data from source to a destination
.1 Click the Transformations tab, and then click Delete All.
.2 Highlight all columns in the Source list.
.3 Highlight all columns in the Destination list, except for the
product_dim_key, and then click New.
.4 In the Create New Transformation dialog box select Copy Column, and
then click OK.
.5 In the Transformation Options dialog box, type copy product in the
Name box.
.6 Click Properties to verify the source to destination column order, and then
click OK.
.7 Click OK to exit out of the Transformation Options dialog box, and then
click OK to exit out of the Transform Data Task Properties dialog box.
APAC BI Technical Training
Lab A: Creating a Simple Data Load
Exercise 3
Adding an Execute SQL Task
In this exercise, you will create a new SQL Server connection to the polaris
database and an Execute SQL Task to delete data from the product_dim
dimension.
To create the Delete connection
.1 On the Connection toolbar, click the Microsoft OLE DB Provider for SQL
Server icon.
.2 In the New connection box, type Delete as the connection name.
.3 In the Database drop down, select polaris.
.4 Click OK.
To add an Execute SQL Task
.1 Click the Execute SQL Task in the Task toolbar.
.2 In the Execute SQL Task Properties dialog box, type Delete in the
Description box.
.3 In the Existing connection list, click Delete.
.4 In the SQL statement box, type DELETE FROM product_dim
.5 Click Parse Query, and then click OK.
.6 Click OK to exit the Execute SQL Task Properties dialog box.
Exercise 4
Defining the Package Workflow
In this exercise, you will define the workflow of the product_dim package as
follows:
Old data should be successfully deleted from the product_dim dimension prior
to the data load from the Northwind database.
To define the package workflow
In this procedure, you will create precedence constraints to meet the workflow
requirements of the product_dim package.
.1 Click the Delete Execute SQL Task, press the CTRL key, and click the
Product Source NW connection.
.2 Click the Workflow menu, and then click On Success.
APAC BI Technical Training
Lab A: Creating a Simple Data Load
Exercise 5
Finishing Up and Executing
In this exercise, you will save the product_dim package to SQL Server,
execute the package, and then verify the package results.
To save the product_dim package to SQL Server
.1 Click the Package menu, and then 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,
click OK, and then close DTS Package Designer.
Parameter
Value
Package name
product_dim
Location
SQL Server
To execute the product_dim package in DTS Package Designer
.1 Click the Execute play button on DTS Package Designer toolbar, and then
click OK in Package Execution Results dialog box.
.2 If any step failed, double-click the steps name to display the error message
for the step.
.3 Click Done.
To verify package results
.1 Click Start, point to Programs, point to Microsoft SQL Server, and then
click Query Analyzer.
.2 In the Connect to SQL Server dialog box, click OK.
.3 Go to the database list in the toolbar, and then click polaris.
.4 Type the following query:
SELECT * FROM product_dim
.5 Click the Execute Query play button on the toolbar.
.6 Review data results. You should have 77 records in the product_dim
dimension. Verify one record of data by using the following table.
Column name
Value
product_dim_key
<Values may vary>
product_id_app
product_name
Chai
reorder level
10
obsolete flag
category_name
Beverages
category_description
Soft drinks, coffees, teas, beers, and ales
unit_price
18.0000
quantity_per_unit
10 boxes x 20 bags
APAC BI Technical Training
