SQL Server

SQL Server Integration Services : A Data Transformation Requirement

11/28/2010 11:36:16 AM
Let’s consider a true-life data export requirement that is best served by using SSIS. The requirement is for a small business intelligence data mart (on SQL Server 2008) to be spun off each week from the main OLTP database (also on SQL Server 2008) that addresses a product sales manager’s need to see the total year-to-date business that a customer has generated. This data mart is merely a standard SQL Server database and tables that have been transformed (that is, aggregated) for a targeted purpose. As an option, the manager would also like to spin off an Excel version of this (or at least a comma-delimited .csv file for Excel), which will be distributed via email to all salespeople in the region. This overall requirement has been named “Hot Customers Plus” to indicate the emphasis on customers who are generating significant business for the company (and they will be customers who have made greater than $5,000 worth of orders). The offloaded data mart is on a separate machine from the critical OLTP system for all the right reasons; no reporting or ad hoc queries are done against the OLTP system. This process must be repeated on a weekly basis as a total refresh (see Figure 1). We use the AdventureWorks2008 database for this example.
Figure 1. Creating a data mart and a comma-delimited flat file from an OLTP database, using SSIS.

Essentially, order data from the OLTP database (contained in the Customer, Product, Store, SalesTerritory, SalesOrderHeader, and SalesOrderDetails tables) must be aggregated for every order for each customer. In addition, the total amount to be stored in the YTDTotalSales column in the data mart has to be extended out to reflect the summary of each product for each customer. The manager is also interested only in customers who are ordering products that total $5,000 or more. Although the requirements are many, SSIS should be able to handle all this with no problem.

So that you can get a good feel for the two main SSIS tool capabilities, this chapter takes you through generating the solution to this requirement using the SSIS Wizard first, and then we walk through the same solution using SSIS Designer.

Other -----------------
- SQL Server 2008 : SSIS Tools and Utilities
- SQL Server 2008 : SSIS Architecture and Concepts
- SQL Server 2008 : SQL Server Integration Services - SSIS Basics
- Defensive Error Handling : Using Transactions and XACT_ABORT to Handle Errors
- Managing Security Within the Database Engine : Securables
- Managing Security Within the Database Engine : Database Security
- Managing Security Within the Database Engine : Creating SQL Server Principals
- SQL Server 2008 : Performance Tuning - Locks, Blocking, and Deadlocks
- SQL Server 2008 : Performance Tuning - Tracing
- SQL Server 2008 : Implementing Error Handling - Managing and Raising User-Defined Errors
- SQL Server 2008 : Implementing Error Handling - Understanding Errors
- Implementing SQL Server Objects Using Managed Code (part 2)
- Implementing SQL Server Objects Using Managed Code (part 1)
- Encryption Catalog Views
- Built-In Cryptographic Functions
- SQL server 2008 : Managing Security - Permissions
- SQL server 2008 : Managing Security - Schemas
- SQL server 2008 : Managing Security - Users
- SQL server 2008 : Managing Security - Roles
- SQL Server 2008 : Managing Remote Servers
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8