Microsoft SQL Server 2008 R2 Unleashed- P216:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 2114 CHAPTER 52 SQL Server Integration Services FIGURE Invoking the Query Builder interface from the SQL Task Editor dialog. FIGURE The Query Builder GUI for developing SQL queries. Running the SSIS Wizard 2115 52 FIGURE The SSIS Expression Builder GUI for developing expressions. 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 . We use the AdventureWorks2008 database for this example. 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. Running the SSIS Wizard The SSIS Wizard is a streamlined interface solely used to generate SSIS packages for importing or exporting data. It is really quite powerful and provides an .
