BIData 201: Assignment 06: Creating an SSIS ETL Package

Tim Pauley

BIData 201b, Assignment 06

Due: November 13, 2016

Blog Location:

Assignment 06: Creating an SSIS ETL Package

In this “hands on” activity you will learn about fill a Data Warehouse using an SSIS package for ETL processing. This activity will take you about 15 hours, so plan accordingly! This is one of the longest modules so please take note!!!

The activity consists of the follow steps:

1. Watch the module videos.
2. Review the Instructor’s spreadsheet.
3. Review the Instructor’s ETL processing script.
4. Read the 7^th and 8^th chapters of your textbook.
5. Perform the Exercises in the textbook.
6. Configure an SSIS ETL package using your instructor’s ETL script.
7. Document your SSIS Objects.
8. Submit a BI Solution to the Canvas site.

Watch the module videos

Please watch the videos I created about working on reviewing Module 6, called “Review of Mod06. Later, watch the videos I made about the chapter’s exercise. You can find these videos here: Chapter Exercise Tips.

Review the Instructor’s spreadsheet

I have provided you with my version of the spreadsheet, called InstructorsBISolutionWorksheet.xlsx, so please use that version for the assignment. Please review the Transformations and ETL functions noted on the Data Warehouse tab, by your project manager (me, your instructor).  

Review the Instructor’s ETL processing script

Your instructor has created their version of the ETL processing script. This script will be use along with an SSIS package to fill the AdventureWorks data warehouse. Please review this script by comparing it to the one you created in Module 06.

Important: Once you have reviewed the differences, please use the instructor’s version to configure the SSIS package.

Read the seventh and eighth chapters of your textbook.

These chapters are very long so, you will be covering about 100 pages. The good news is that there are a lot of pictures, but the bad news is that it is still 100 pages! Please start early!

Assignment 06 CREATING AN SSIS PROJECT

In this assignment, you create an SSIS project to perform the ETL processing required to fill the Publication Industries data warehouse, DWPubsSales.

Step 1: Create new blank solution in VS

This is a new project in Visual Studio

Step 2: Folder structure

I also added document by adding an existing items

Step 3: Run scripts and make sure they execute successfully


Here are the scripts I ran

Step 3: Run scripts and make sure they execute successfully

01_StarterScript Create the OLTP DB

/**************************************************************

 Data Warehouse Design LAB

(STEP 1) Create a lite version of the Northwind database

(STEP 2) Create a new Data Warehouse called DWNorthwindLite

        a) The DW should have 3 regular dimensions and 1 fact dimensions

        b) The DW should have one fact table and 2 measures

 *************************************************************/

/***** (STEP 1) Create a lite version of the Northwind database *****/

-- Run this script to create the database you will need for the lab

If Exists(Select name from master.dbo.sysdatabases Where Name = 'NorthwindLite')

Begin

      USE [master];

      ALTER DATABASE [NorthwindLite] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

      DROP DATABASE [NorthwindLite];

End;

Go

Create Database NorthwindLite;

Go

USE NorthwindLite;

Go

SELECT

 [ProductID]

,[ProductName]

,[CategoryID]

INTO [Products]

FROM [Northwind].[dbo].[Products];

Go

SELECT

 [CategoryID]

,[CategoryName]

INTO [Categories]

FROM [Northwind].[dbo].[Categories];

Go

SELECT

 [CustomerID]

,[CompanyName]

,[ContactName]

,[Address]

,[City]

,[Country]

INTO [Customers]

FROM [Northwind].[dbo].[Customers];

Go

SELECT

  [OrderID]

 ,[CustomerID]

 ,[OrderDate]

INTO [Orders]

FROM [Northwind].[dbo].[Orders];

Go

SELECT [OrderID]

,[ProductID]

,[UnitPrice]

,[Quantity]

INTO [OrderDetails]

FROM [Northwind].[dbo].[Order Details];

Go

-- Add Primary keys

ALTER TABLE dbo.Products ADD CONSTRAINT

      PK_Products PRIMARY KEY CLUSTERED (ProductID );

ALTER TABLE dbo.Categories ADD CONSTRAINT

      PK_Categories PRIMARY KEY CLUSTERED ( CategoryID );

ALTER TABLE dbo.Customers ADD CONSTRAINT

      PK_Customers PRIMARY KEY CLUSTERED ( CustomerID );

ALTER TABLE dbo.Orders ADD CONSTRAINT

      PK_Orders PRIMARY KEY CLUSTERED ( OrderId );

ALTER TABLE dbo.OrderDetails ADD CONSTRAINT

      PK_OrderDetais PRIMARY KEY CLUSTERED ( OrderId, ProductId );

Go

-- Add For

ALTER TABLE dbo.Products ADD CONSTRAINT

      FK_Products_Categories FOREIGN KEY(      CategoryID )

      REFERENCES dbo.Categories( CategoryID );

ALTER TABLE dbo.OrderDetails ADD CONSTRAINT

      FK_OrderDetails_Products FOREIGN KEY ( ProductID )

      REFERENCES dbo.Products  ( ProductID );

ALTER TABLE dbo.OrderDetails ADD CONSTRAINT

      FK_OrderDetails_Orders FOREIGN KEY ( OrderID )

      REFERENCES dbo.Orders ( OrderID ) ;

ALTER TABLE dbo.Orders ADD CONSTRAINT

      FK_Orders_Customers FOREIGN KEY ( CustomerID )

      REFERENCES dbo.Customers (      CustomerID   );

Go

Select 'New database made with these objects:'

Select Name From SysObjects Where xtype in ('u', 'pk', 'f')

SELECT [TABLE_NAME]

      ,[COLUMN_NAME]

      ,[IS_NULLABLE]

      ,[DATA_TYPE]

      ,[CHARACTER_MAXIMUM_LENGTH]

      ,[NUMERIC_PRECISION]

      ,[NUMERIC_SCALE]

FROM [NorthwindLite].[INFORMATION_SCHEMA].[COLUMNS];

Go

/* (STEP 2) Create a new Data Warehouse DWNorthwindLite

        a) The DW should have 3 regular dimensions and 1 fact dimensions

        b) The DW should have one fact table and 2 measures

*/

02_SetupScript Create the DW DB

/**************************************************************

 Create the Data Warehouse

*************************************************************/

--****************** [DWNorthwindLite] *********************--

-- This file will drop and create the [DWNorthwindLite]

-- database, with all its objects.

--****************** Instructors Version ***************************--

USE [master]

GO

If Exists (Select * from Sysdatabases Where Name = 'DWNorthwindLite')

      Begin

             ALTER DATABASE [DWNorthwindLite] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

             DROP DATABASE [DWNorthwindLite]

      End

GO

Create Database [DWNorthwindLite]

Go

--********************************************************************--

-- Create the Tables

--********************************************************************--

USE [DWNorthwindLite]

Go

/****** [dbo].[DimProducts] ******/

CREATE TABLE DWNorthwindLite.dbo.DimProducts(

       ProductKey int    IDENTITY              NOT NULL

      ,ProductID int                                 NOT NULL

      ,ProductName nVarchar(100)               NOT NULL

      ,ProductCategoryID int                  NOT NULL

      ,ProductCategoryName nVarchar(100) NOT NULL

      ,StartDate int                                 NOT NULL

      ,EndDate int                             NULL

      ,IsCurrent char(3)                       NOT NULL

      CONSTRAINT PK_DimProducts PRIMARY KEY (ProductKey)

)

Go

/****** [dbo].[DimCustomers] ******/

CREATE TABLE DWNorthwindLite.dbo.DimCustomers(

       CustomerKey int IDENTITY    NOT NULL

      ,CustomerID nchar(5)               NOT NULL

      ,CustomerName nVarchar(100)       NOT NULL

      ,CustomerCity nVarchar(100)       NOT NULL

      ,CustomerCountry nVarchar(100) NOT NULL

      ,StartDate int                           NOT NULL

      ,EndDate int                       NULL

      ,IsCurrent char(3)                 NOT NULL

      CONSTRAINT PK_DimCustomers PRIMARY KEY (CustomerKey)

)

Go

/****** [dbo].[DimDates] ******/

CREATE TABLE DWNorthwindLite.dbo.DimDates(

       DateKey int                NOT NULL

      ,USADateName nVarchar(100) NOT NULL

      ,MonthKey int                      NOT NULL

      ,MonthName nVarchar(100)   NOT NULL

      ,QuarterKey int                    NOT NULL

      ,QuarterName nVarchar(100) NOT NULL

      ,YearKey int                NOT NULL

      ,YearName nVarchar(100)     NOT NULL

      CONSTRAINT PK_DimDates PRIMARY KEY (DateKey)

)

Go

/****** [dbo].[FactOrders] ******/

CREATE TABLE DWNorthwindLite.dbo.FactOrders(

       OrderID int                    NOT NULL

      ,CustomerKey int                NOT NULL

      ,OrderDateKey int               NOT NULL

      ,ProductKey int                       NOT NULL

      ,ActualOrderUnitPrice money    NOT NULL

      ,ActualOrderQuantity int NOT NULL

      CONSTRAINT PK_FactOrders PRIMARY KEY (OrderID,CustomerKey,OrderDateKey,ProductKey)

)

Go

--********************************************************************--

-- Create the FOREIGN KEY CONSTRAINTS

--********************************************************************--

ALTER TABLE DWNorthwindLite.dbo.FactOrders

  ADD CONSTRAINT FK_FactOrders_DimProducts

  FOREIGN KEY (ProductKey) REFERENCES DimProducts(ProductKey)

ALTER TABLE DWNorthwindLite.dbo.FactOrders

  ADD CONSTRAINT FK_FactOrders_DimCustomers

  FOREIGN KEY (CustomerKey) REFERENCES DimCustomers(CustomerKey)

ALTER TABLE DWNorthwindLite.dbo.FactOrders

  ADD CONSTRAINT FK_FactOrders_DimDates

  FOREIGN KEY (OrderDateKey) REFERENCES DimDates(DateKey)

--********************************************************************--

-- Review the results of this script

--********************************************************************--

Select 'Database Created'

Select Name, xType, CrDate from SysObjects

Where xType in ('u', 'PK', 'F')

Order By xType desc, Name

You need to create an SSIS package using the code the instructor’s ELT codes. It will look similar to this example:

Document your SSIS Objects

There is a worksheet in InstructorsBISolutionWorksheet.xlsx called ELT objects. You will use this to list all the SSIS objects you create. When you are done, it should look like this one...

Submit a BI Solution to the Canvas site

After you have configured and tested your SSIS project, ZIP the entire BI solution folder and upload it to the Canvas web site.

You’re done!