You are previewing Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services.
O'Reilly logo
Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Book Description

A unique book-and-video package for Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

If you need a practical, hands-on introduction to Microsoft SQL Server 2008 Integration Services (SSIS), this book and video package from foremost SSIS authority Brian Knight gets you thoroughly up to speed. Each lesson in the book includes three major components: a thorough description of how each SSIS feature or process works; a tutorial that walks you through the process or technique; and a video lesson that demonstrates it.

If you take full advantage of this comprehensive package, you will gain enough experience to tackle your first SSIS project with confidence.

  • SQL Server Integration Services 2008 (SSIS) builds on the revolutionary database product suite first introduced by Microsoft in 2005 and is a powerful tool for performing extraction, transformation, and load (ETL) operations essential to data warehousing

  • If you're just learning SSIS, the step-by-step tutorials in this book and video training package will ready you to tackle your own first project

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of Contents

  1. Copyright
  2. Credits
  3. About the Authors
  4. Acknowledgments
  5. Preface
    1. Who This Book Is For
    2. What This Book Covers
    3. How This Book Is Structured
    4. What This Book Covers
    5. Instructional Videos on DVD
    6. Conventions
    7. Supporting Packages and Code
    8. Errata
    9. p2p.wrox.com
  6. Welcome to SSIS
    1. Import and Export Wizard
    2. The Business Intelligence Development Studio
    3. Architecture
      1. Packages
      2. Tasks
      3. Data Flow Elements
      4. Sources
      5. Destinations
      6. Transformations
    4. Editions of SQL Server 2008
    5. Summary
  7. I. Installation and Getting Started
    1. 1. Moving Data with the Import and Export Wizard
      1. 1.1. Try It
        1. 1.1.1. Lesson Requirements
        2. 1.1.2. Hints
        3. 1.1.3. Step-by-Step
    2. 2. Installing SQL Server Integration Services
    3. 3. Installing the Sample Databases
      1. 3.1. Try It
        1. 3.1.1. Lesson Requirements
        2. 3.1.2. Hints
        3. 3.1.3. Step-by-Step
    4. 4. Creating a Solution and Project
      1. 4.1. Try It
        1. 4.1.1. Lesson Requirements
        2. 4.1.2. Hints
        3. 4.1.3. Step-by-Step
    5. 5. Exploring Business Intelligence Development Studio
      1. 5.1. The Solution Explorer
      2. 5.2. The Properties Window
      3. 5.3. The Toolbox
      4. 5.4. The BIDS Design Environment
    6. 6. Creating Your First Package
      1. 6.1. Creating and Using Connection Managers
      2. 6.2. Using and Configuring Tasks
      3. 6.3. Exploring Package Encryption
      4. 6.4. Executing Packages
      5. 6.5. Try It
        1. 6.5.1. Lesson Requirements
        2. 6.5.2. Hints
        3. 6.5.3. Step-by-Step
  8. II. Tasks
    1. 7. Connecting Control Flow Tasks with Precedence Constraints
      1. 7.1. Try It
        1. 7.1.1. Lesson Requirements
        2. 7.1.2. Hints
        3. 7.1.3. Step-by-Step
    2. 8. Manipulating Files with the File System Task
      1. 8.1. Try It
        1. 8.1.1. Lesson Requirements
        2. 8.1.2. Hints
        3. 8.1.3. Step-by-Step
    3. 9. Coding Custom Script Tasks
      1. 9.1. Try It
        1. 9.1.1. Lesson Requirements
        2. 9.1.2. Hints
        3. 9.1.3. Step-by-Step
    4. 10. Using the Execute SQL Task
      1. 10.1. Try It
        1. 10.1.1. Lesson Requirements
        2. 10.1.2. Hints
        3. 10.1.3. Step-by-Step
    5. 11. Using the Send Mail Task
      1. 11.1. Try It
        1. 11.1.1. Lesson Requirements
        2. 11.1.2. Hints
        3. 11.1.3. Step-by-Step
    6. 12. Using the FTP Task
      1. 12.1. Try It
        1. 12.1.1. Lesson Requirements
        2. 12.1.2. Hints
        3. 12.1.3. Step-by-Step
    7. 13. Creating a Data Flow
      1. 13.1. Try It
        1. 13.1.1. Lesson Requirements
        2. 13.1.2. Hints
        3. 13.1.3. Step-by-Step
  9. III. Data Flow
    1. 14. Extracting Data from Sources
      1. 14.1. OLE DB Source
        1. 14.1.1. Try It
        2. 14.1.2. Lesson Requirements
        3. 14.1.3. Hints
        4. 14.1.4. Step-by-Step
      2. 14.2. Excel Source
        1. 14.2.1. Try It
          1. 14.2.1.1. Lesson Requirements
          2. 14.2.1.2. Hints
          3. 14.2.1.3. Step-by-Step
      3. 14.3. Flat File Source
        1. 14.3.1. Try It
          1. 14.3.1.1. Lesson Requirements
          2. 14.3.1.2. Hints
          3. 14.3.1.3. Step-by-Step
    2. 15. Loading Data to a Destination
      1. 15.1. OLE DB Destination
        1. 15.1.1. Try It
          1. 15.1.1.1. Lesson Requirements
          2. 15.1.1.2. Hints
          3. 15.1.1.3. Step-by-Step
      2. 15.2. Flat File Destination
        1. 15.2.1. Try It
          1. 15.2.1.1. Lesson Requirements
          2. 15.2.1.2. Hints
          3. 15.2.1.3. Step-by-Step
      3. 15.3. Excel Destination
        1. 15.3.1. Try It
          1. 15.3.1.1. Lesson Requirements
          2. 15.3.1.2. Hints
          3. 15.3.1.3. Step-by-Step
    3. 16. Changing Data Types with the Data Conversion Transform
      1. 16.1. Try It
        1. 16.1.1. Lesson Requirements
        2. 16.1.2. Hints
        3. 16.1.3. Step-by-Step
    4. 17. Creating and Replacing Columns with the Derived Column Transform
      1. 17.1. Try It
        1. 17.1.1. Lesson Requirements
        2. 17.1.2. Hints
        3. 17.1.3. Step-by-Step
    5. 18. Rolling Up Data with The Aggregate Transform
      1. 18.1. Try It
        1. 18.1.1. Lesson Requirements
        2. 18.1.2. Hints
        3. 18.1.3. Step-by-Step
    6. 19. Ordering Data with the Sort Transform
      1. 19.1. Try It
        1. 19.1.1. Lesson Requirements
        2. 19.1.2. Hints
        3. 19.1.3. Step-by-Step
    7. 20. Joining Data with the Lookup Transform
      1. 20.1. Cache Modes
        1. 20.1.1. Try It
          1. 20.1.1.1. Lesson Requirements
          2. 20.1.1.2. Hints
          3. 20.1.1.3. Step-by-Step
      2. 20.2. The Cache Connection Manager and Transform
        1. 20.2.1. Try It
          1. 20.2.1.1. Lesson Requirements
          2. 20.2.1.2. Hints
          3. 20.2.1.3. Step-by-Step
    8. 21. Auditing Data with the Row Count Transform
      1. 21.1. Try It
        1. 21.1.1. Lesson Requirements
        2. 21.1.2. Hints
        3. 21.1.3. Step-by-Step
    9. 22. Combining Multiple Inputs with the Union All Transform
      1. 22.1. Try It
        1. 22.1.1. Lesson Requirements
        2. 22.1.2. Hints
        3. 22.1.3. Step-by-Step
    10. 23. Cleansing Data with the Script Component
      1. 23.1. Try It
        1. 23.1.1. Lesson Requirements
        2. 23.1.2. Hints
        3. 23.1.3. Step-by-Step
    11. 24. Separating Data with the Conditional Split Transform
      1. 24.1. Try It
        1. 24.1.1. Lesson Requirements
        2. 24.1.2. Hints
        3. 24.1.3. Step-by-Step
    12. 25. Altering Rows with the OLE DB Command Transform
      1. 25.1. Try It
        1. 25.1.1. Lesson Requirements
        2. 25.1.2. Hints
        3. 25.1.3. Step-by-Step
    13. 26. Handling Bad Data with the Fuzzy Lookup Transform
      1. 26.1. Try It
        1. 26.1.1. Lesson Requirements
        2. 26.1.2. Hints
        3. 26.1.3. Step-by-Step
    14. 27. Removing Duplicates with the Fuzzy Grouping Transform
      1. 27.1. Try It
        1. 27.1.1. Lesson Requirements
        2. 27.1.2. Hints
        3. 27.1.3. Step-by-Step
  10. IV. Variables and Expressions
    1. 28. Making a Package Dynamic with Variables
      1. 28.1. Try It
        1. 28.1.1. Lesson Requirements
        2. 28.1.2. Hints
        3. 28.1.3. Step-by-Step
    2. 29. Making a Connection Dynamic with Expressions
      1. 29.1. Try It
        1. 29.1.1. Lesson Requirements
        2. 29.1.2. Hints
        3. 29.1.3. Step-by-Step
    3. 30. Making a Task Dynamic with Expressions
      1. 30.1. Try It
        1. 30.1.1. Lesson Requirements
        2. 30.1.2. Hints
        3. 30.1.3. Step-by-Step
  11. V. Containers
    1. 31. Using Sequence Containers to Organize a Package
      1. 31.1. Try It
        1. 31.1.1. Lesson Requirements
        2. 31.1.2. Hints
        3. 31.1.3. Step-by-Step
    2. 32. Using For Loop Containers to Repeat Control Flow Tasks
      1. 32.1. Try It
        1. 32.1.1. Lesson Requirements
        2. 32.1.2. Hints
        3. 32.1.3. Step-by-Step
    3. 33. Using the Foreach Loop Container to Loop Through a Collection of Objects
      1. 33.1. Try It
        1. 33.1.1. Lesson Requirements
        2. 33.1.2. Hints
        3. 33.1.3. Step-by-Step
  12. VI. Configuration
    1. 34. Easing Deployment with Configuration Tables
      1. 34.1. Try It
        1. 34.1.1. Lesson Requirements
        2. 34.1.2. Hints
        3. 34.1.3. Step-by-Step
    2. 35. Easing Deployment with Configuration Files
      1. 35.1. Try It
        1. 35.1.1. Lesson Requirements
        2. 35.1.2. Hints
        3. 35.1.3. Step-by-Step
    3. 36. Configuring Child Packages
      1. 36.1. Configuring an Execute Package Task
      2. 36.2. Configuring a Child Package
      3. 36.3. Try It
        1. 36.3.1. Lesson Requirements
        2. 36.3.2. Hints
        3. 36.3.3. Step-by-Step
  13. VII. Troubleshooting and Logging
    1. 37. Logging Package Data
      1. 37.1. Try It
        1. 37.1.1. Lesson Requirements
        2. 37.1.2. Hints
        3. 37.1.3. Step-by-Step
    2. 38. Using Event Handlers
      1. 38.1. Creating Event Handlers
      2. 38.2. Common Uses for Event Handlers
      3. 38.3. Try It
        1. 38.3.1. Lesson Requirements
        2. 38.3.2. Hints
        3. 38.3.3. Step-by-Step
    3. 39. Troubleshooting Errors
      1. 39.1. Working in the Progress Tab
      2. 39.2. Troubleshooting Steps
      3. 39.3. Try It
        1. 39.3.1. Lesson Requirements
        2. 39.3.2. Hints
        3. 39.3.3. Step-by-Step
    4. 40. Using Data Viewers
      1. 40.1. Try It
        1. 40.1.1. Lesson Requirements
        2. 40.1.2. Hints
        3. 40.1.3. Step-by-Step
    5. 41. Using Breakpoints
      1. 41.1. Try It
        1. 41.1.1. Lesson Requirements
        2. 41.1.2. Hints
        3. 41.1.3. Step-by-Step
  14. VIII. Deployment and Administration
    1. 42. Deploying Packages
      1. 42.1. Deploying Packages Using a Deployment Utility
      2. 42.2. Other Methods of Deploying Packages
      3. 42.3. Try It
        1. 42.3.1. Lesson Requirements
        2. 42.3.2. Hints
        3. 42.3.3. Step-by-Step
    2. 43. Configuring the SSIS Service
      1. 43.1. Try It
        1. 43.1.1. Lesson Requirements
        2. 43.1.2. Hints
        3. 43.1.3. Step-by-Step
    3. 44. Securing SSIS Packages
      1. 44.1. Try It
        1. 44.1.1. Lesson Requirements
        2. 44.1.2. Hints
        3. 44.1.3. Step-by-Step
    4. 45. Running SSIS Packages
      1. 45.1. Try It
        1. 45.1.1. Lesson Requirements
        2. 45.1.2. Hints
        3. 45.1.3. Step-by-Step
    5. 46. Scheduling Packages
      1. 46.1. Using Proxy Accounts
      2. 46.2. Try It
        1. 46.2.1. Lesson Requirements
        2. 46.2.2. Hints
        3. 46.2.3. Step-by-Step
  15. IX. Data Warehousing
    1. 47. Loading a Dimension Table
      1. 47.1. Try It
        1. 47.1.1. Lesson Requirements
        2. 47.1.2. Hints
        3. 47.1.3. Step-by-Step
    2. 48. Loading a Fact Table
      1. 48.1. Try It
        1. 48.1.1. Lesson Requirements
        2. 48.1.2. Hints
        3. 48.1.3. Step-by-Step
  16. X. Wrap Up and Review
    1. 49. Bringing It All Together
      1. 49.1. Lesson Requirements
      2. 49.2. Hints
      3. 49.3. Step-by-Step
    2. A. SSIS Component Crib Notes
      1. A.1. When to Use Control Flow Tasks
      2. A.2. When to Use Data Flow Transforms
    3. B. Problem and Solution Crib Notes
  17. C. What's on the DVD?
    1. C.1. System Requirements
    2. C.2. Using the DVD
    3. C.3. What's on the DVD
    4. C.4. Troubleshooting
    5. C.5. Customer Care