Professional SQL Server™ 2005 Integration Services

Professional SQL Server™ 2005 Integration Services

by Brian Knight, Allan Mitchell, Darren Green, Douglas Hinson, Kathi Kellenberger, Andy Leonard, Erik Veerman, Jason Gerard, Haidong Ji, Mike Murphy
Released January 2006
Publisher(s): Wrox
ISBN: 9780764584350

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

This book will help you get past the initial learning curve quickly so that you can get started using SSIS to transform data, create a workflow, or maintain your SQL Server. Offering you hands-on guidance, you'll learn a new world of integration possibilities and be able to move away from scripting complex logic to programming tasks using a full-featured language.

What you will learn from this book

  • Ways to quickly move and transform data

  • How to configure every aspect of SSIS

  • How to interface SSIS with web services and XML

  • Techniques to scale the SSIS and make it more reliable

  • How to migrate DTS packages to SSIS

  • How to create your own custom tasks and user interfaces

  • How to create an application that interfaces with SSIS to manage the environment

  • A detailed usable case study for a complete ETL solution

Who this book is for

This book is for developers, DBAs, and users who are looking to program custom code in all of the .NET languages. It is expected that you know the basics of how to query the SQL Server and have some fundamental programming skills.

Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Dedication
  5. Acknowledgments
  6. Foreword
  7. Introduction
  8. Welcome to SQL Server Integration Services
    1. What's New in SQL Server 2005 SSIS
    2. Architecture
    3. Precedence Constraints
    4. Containers
    5. Variables
    6. Data Flow Elements
    7. Error Handling and Logging
    8. Editions of SQL Server 2005
    9. Summary
  9. The SSIS Tools
    1. Import and Export Wizard
    2. Package Installation Wizard
    3. Business Intelligence Development Studio
    4. Creating Your First Package
    5. The Solution Explorer Window
    6. The SSIS Package Designer
    7. Summary
  10. SSIS Tasks
    1. Shared Properties
    2. Execute SQL Task
    3. Bulk Insert Task
    4. Using the Bulk Insert and Execute SQL Tasks
    5. Data Flow Task
    6. Execute Process Task
    7. File System Task
    8. FTP Task
    9. Using the File System and FTP Task
    10. Execute Package Task
    11. Script and ActiveX Tasks
    12. Send Mail Task
    13. Message Queue Task
    14. Web Service Task
    15. WMI Data Reader and Event Watcher Task
    16. XML Task
    17. SQL Server Analysis Services Execute DDL and Processing Tasks
    18. Data Mining Query Task
    19. The Expression Page
    20. Summary
  11. Containers and Data Flow
    1. Containers
    2. Sources
    3. Destinations
    4. Transformations
    5. Data Flow Example
    6. Data Viewers
    7. Summary
  12. Creating an End-to-End Package
    1. Basic Transformation Tutorial
    2. Typical Mainframe ETL with Data Scrubbing
    3. Looping and the Dynamic Task
    4. Summary
  13. Advanced Tasks and Transforms
    1. Execute SQL Task
    2. Import Column
    3. Using Temp Tables in SSIS Package Development
    4. Export Column
    5. Row Count
    6. OLE DB Command
    7. Term Extraction
    8. Term Lookup
    9. Fuzzy Lookup
    10. Fuzzy Grouping
    11. Pivot Transform
    12. Unpivot
    13. Slowly Changing Dimension
    14. Database Object-Level Tasks
    15. Summary
  14. Scripting in SSIS
    1. Scripting Overview
    2. Expressions
    3. Script Tasks
    4. Using .NET Assemblies
    5. Structured Exception Handling
    6. Script Component
    7. Summary
  15. Accessing Heterogeneous Data
    1. Excel
    2. Access
    3. Oracle
    4. Web Services
    5. XML Data
    6. Summary
  16. Reliability and Scalability
    1. Restarting Packages
    2. Package Transactions
    3. Error Outputs
    4. Scaling Out
    5. Summary
  17. Understanding the Integration Services Engine
    1. The Integration Services Engine: An Analogy
    2. Understanding the SSIS Data Flow and Control Flow
    3. Enterprise Workflows with the Control Flow
    4. Enterprise Data Processing with the Data Flow
    5. Summary
  18. Applying the Integration Services Engine
    1. That Was Then: DTS
    2. This Is Now: Integration Services
    3. Pipeline Performance Monitoring
    4. Summary
  19. DTS 2000 Migration and Metadata Management
    1. Migrating DTS 2000 Packages to SSIS
    2. Using the Package Migration Wizard
    3. Running DTS 2000 Packages under SSIS
    4. Package Metadata and Storage Management
    5. Summary
  20. Error and Event Handling
    1. Precedence Constraints
    2. Event Handling
    3. Breakpoints
    4. Checkpoints
    5. Logging
    6. Summary
  21. Programming and Extending SSIS
    1. The Sample Components
    2. The Pipeline Component Methods
    3. Building the Components
    4. Debugging Components
    5. Summary
  22. Adding a User Interface to Your Component
    1. Three Key Steps
    2. Building the User Interface
    3. Further Development
    4. Summary
  23. External Management and WMI Task Implementation
    1. External Management with Managed Code
    2. Application Object Maintenance Operations
    3. Package Log Providers
    4. Package Configurations
    5. Windows Management Instrumentation Tasks
    6. Summary
  24. Using SSIS with External Applications
    1. RSS In, Reporting Services Report Out
    2. InfoPath Document
    3. ASP.NET Application
    4. Summary
  25. SSIS Software Development Life Cycle
    1. Introduction to Software Development Life Cycles
    2. Versioning and Source Code Control
    3. Code Deployment and Promotion from Development to Test to Production
    4. Summary
  26. Case Study: A Programmatic Example
    1. Background
    2. Business Problem
    3. Solution Summary
    4. Solution Architecture
    5. Data Architecture
    6. Case Study Load Package
    7. Case Study Process Package
    8. Running in SQL Agent
    9. Summary
  27. Index

Product information

  • Title: Professional SQL Server™ 2005 Integration Services
  • Author(s): Brian Knight, Allan Mitchell, Darren Green, Douglas Hinson, Kathi Kellenberger, Andy Leonard, Erik Veerman, Jason Gerard, Haidong Ji, Mike Murphy
  • Release date: January 2006
  • Publisher(s): Wrox
  • ISBN: 9780764584350