Programming4us
         
 
 
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
 
 
Most View
- SharePoint 2010 : Modify a View
- Benchmarking Current Rankingstages of SEO
- Windows Phone 7 : Browsing the Web - Browsing with Tabs
- Exchange 2007: Manage Public Folder Databases
- Windows Phone 7 : Changing Zune Sync Settings
- Destination: SQL Server 2008 or SQL Server 2008 R2 (part 2) - Upgrading In-Place
- SQL Injection Attacks and Defense : Accessing the File System (part 1) - Reading Files
- Integrating Office 2007 Applications with Windows SharePoint Services 3.0
- SharePoint 2010 : The Client Object Model (part 3) - Writing the JavaScript WebPart
- User-Level Security : Authorization and Impersonation (part 4) - Impersonation
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS