Professional SQL Server® 2008 Integration Services

Professional SQL Server® 2008 Integration Services

by Brian Knight, Erik Veerman, Grant Dickinson, Douglas Hinson, Darren Herbold
Released October 2008
Publisher(s): Wrox
ISBN: 9780470247952

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

  • The new edition of the successful previous version is 25 percent revised and packed with more than 200 pages of new material on the 2008 release of SQL Server Integration Services (SSIS)

  • Renowned author Brian Knight and his expert coauthors show developers how to master the 2008 release of SSIS, which is both more powerful and more complex than ever

  • Case studies and tutorial examples acquired over the three years since the previous edition will contribute to helping illustrate advanced concepts and techniques

  • New chapters include coverage of data warehousing using SSIS, new methods for managing the SSIS platform, and improved techniques for ETL operations

Table of contents

  1. Copyright
  2. About the Authors
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
    2. What This Book Covers
    3. How This Book Is Structured
    4. What You Need to Use This Book
    5. Conventions
    6. Source Code
    7. Errata
    8. p2p.wrox.com
  6. 1. Welcome to SQL Server Integration Services
    1. 1.1. SQL Server SSIS Historical Overview
    2. 1.2. What's New in SSIS
    3. 1.3. Getting Started
      1. 1.3.1. Import and Export Wizard
      2. 1.3.2. The Business Intelligence Development Studio
    4. 1.4. Architecture
      1. 1.4.1. Packages
      2. 1.4.2. Tasks
      3. 1.4.3. Data Source Elements
      4. 1.4.4. Data Source Views
    5. 1.5. Precedence Constraints
      1. 1.5.1. Constraint Value
      2. 1.5.2. Conditional Expressions
    6. 1.6. Containers
    7. 1.7. Variables
    8. 1.8. Data Flow Elements
      1. 1.8.1. Sources
      2. 1.8.2. Destinations
      3. 1.8.3. Transformations
    9. 1.9. Error Handling and Logging
    10. 1.10. Editions of SQL Server
    11. 1.11. Summary
  7. 2. The SSIS Tools
    1. 2.1. Import and Export Wizard
    2. 2.2. Business Intelligence Development Studio
    3. 2.3. Creating Your First Package
    4. 2.4. The Solution Explorer Window
      1. 2.4.1. The Toolbox
      2. 2.4.2. The Properties Windows
      3. 2.4.3. Navigation Pane
      4. 2.4.4. Other Windows
    5. 2.5. The SSIS Package Designer
      1. 2.5.1. Control Flow
        1. 2.5.1.1. Task Grouping
        2. 2.5.1.2. Annotation
      2. 2.5.2. Connection Managers
      3. 2.5.3. Variables
      4. 2.5.4. Data Flow
      5. 2.5.5. Event Handlers
      6. 2.5.6. Package Explorer
      7. 2.5.7. Executing a Package
    6. 2.6. Package Installation Wizard
    7. 2.7. Management Studio
    8. 2.8. Summary
  8. 3. SSIS Tasks
    1. 3.1. SSIS Task Objects
      1. 3.1.1. Using the Task Editor
      2. 3.1.2. The Task Editor Expressions Tab
      3. 3.1.3. Execution Results
      4. 3.1.4. Common Properties
    2. 3.2. Looping and Sequence Tasks
    3. 3.3. Scripting Tasks
      1. 3.3.1. ActiveX Script Task
      2. 3.3.2. Script Task (.NET)
    4. 3.4. Analysis Services Tasks
      1. 3.4.1. Analysis Services Execute DDL Task
      2. 3.4.2. Analysis Services Processing Task
      3. 3.4.3. Data Mining Query Task
    5. 3.5. Data Flow Task
    6. 3.6. Data Preparation Tasks
      1. 3.6.1. Data Profiler
      2. 3.6.2. File System Task
        1. 3.6.2.1. Archiving a File
      3. 3.6.3. FTP Task
        1. 3.6.3.1. Getting a File Using FTP
      4. 3.6.4. Web Service Task
        1. 3.6.4.1. Retrieving Data Using the Web Service Task and XML Source Component
      5. 3.6.5. XML Task
        1. 3.6.5.1. Validating an XML File
    7. 3.7. RDBMS Server Tasks
      1. 3.7.1. Bulk Insert Task
        1. 3.7.1.1. Using the Bulk Insert Task
      2. 3.7.2. Execute SQL Task
        1. 3.7.2.1. Executing a Parameterized SQL Statement
        2. 3.7.2.2. Executing a Batch of SQL Statements
        3. 3.7.2.3. Capturing Singleton Results
        4. 3.7.2.4. Multi-Row Results
        5. 3.7.2.5. Executing a Stored Procedure
        6. 3.7.2.6. Retrieving Output Parameters from a Stored Procedure
    8. 3.8. Workflow Tasks
      1. 3.8.1. Execute Package Task
        1. 3.8.1.1. Setting Up Child Packages to Use Parent Variables
        2. 3.8.1.2. Changing Parent Variables in Child Packages
      2. 3.8.2. Execute Process Task
      3. 3.8.3. Message Queue Task
      4. 3.8.4. Send Mail Task
      5. 3.8.5. WMI Data Reader Task
      6. 3.8.6. WMI Event Watcher Task
        1. 3.8.6.1. Polling a Directory for the Delivery of a File
    9. 3.9. SMO Administration Tasks
      1. 3.9.1. Transfer Database Task
      2. 3.9.2. Transfer Error Messages
      3. 3.9.3. Transfer Logins Task
      4. 3.9.4. Transfer Master Stored Procedures Task
      5. 3.9.5. Transfer Jobs Task
      6. 3.9.6. Transfer SQL Server Objects Task
    10. 3.10. Summary
  9. 4. Containers
    1. 4.1. Task Host Containers
    2. 4.2. Sequence Containers
    3. 4.3. Groups
    4. 4.4. For Loop Container
    5. 4.5. Foreach Loop Container
      1. 4.5.1. Foreach File Enumerator Example
      2. 4.5.2. Foreach ADO Enumerator Example
    6. 4.6. Summary
  10. 5. The Data Flow
    1. 5.1. The Data Flow
    2. 5.2. Data Viewers
    3. 5.3. Sources
      1. 5.3.1. OLE DB Source
      2. 5.3.2. Excel Source
      3. 5.3.3. Flat File Source
        1. 5.3.3.1. FastParse Option
        2. 5.3.3.2. MultiFlatFile Connection Managers
      4. 5.3.4. Raw File Source
      5. 5.3.5. XML Source
      6. 5.3.6. ADO.NET Source
    4. 5.4. Destinations
      1. 5.4.1. Data Mining Model Training
      2. 5.4.2. DataReader Destination
      3. 5.4.3. Dimension and Partition Processing
      4. 5.4.4. Excel Destination
      5. 5.4.5. Flat File Destination
      6. 5.4.6. OLE DB Destination
      7. 5.4.7. Raw File Destination
      8. 5.4.8. Recordset Destination
      9. 5.4.9. SQL Server and Mobile Destinations
    5. 5.5. Transformations
      1. 5.5.1. Synchronous versus Asynchronous Transformations
      2. 5.5.2. Aggregate
      3. 5.5.3. Audit
      4. 5.5.4. Cache Transform
      5. 5.5.5. Character Map
      6. 5.5.6. Conditional Split
      7. 5.5.7. Copy Column
      8. 5.5.8. Data Conversion
      9. 5.5.9. Data Mining Query
      10. 5.5.10. Derived Column
      11. 5.5.11. Export Column
      12. 5.5.12. Fuzzy Lookup
      13. 5.5.13. Fuzzy Grouping
      14. 5.5.14. Import Column
      15. 5.5.15. Lookup Transform
      16. 5.5.16. Merge Transform
      17. 5.5.17. Merge Join
      18. 5.5.18. Multicast
      19. 5.5.19. OLE DB Command
      20. 5.5.20. Percentage and Row Sampling
      21. 5.5.21. Pivot Transform
      22. 5.5.22. Unpivot
      23. 5.5.23. Row Count
      24. 5.5.24. Script Component
      25. 5.5.25. Slowly Changing Dimension
      26. 5.5.26. Sort
      27. 5.5.27. Term Extraction
      28. 5.5.28. Term Lookup
      29. 5.5.29. Union All
    6. 5.6. Data Flow Example
    7. 5.7. Summary
  11. 6. Using Expressions and Variables
    1. 6.1. The Paradigm
      1. 6.1.1. Expression Overview
      2. 6.1.2. Variable Overview
    2. 6.2. Understanding Data Types
      1. 6.2.1. SSIS Data Types
      2. 6.2.2. Additional Date and Time Type Support
      3. 6.2.3. Wrong Data Types and Sizes Can Affect Performance
      4. 6.2.4. Unicode and Non-Unicode Conversion Issues
      5. 6.2.5. Casting in SSIS Expressions
    3. 6.3. Using Variables
      1. 6.3.1. Defining Variables
      2. 6.3.2. Variable Data Types
    4. 6.4. Working with Expressions
      1. 6.4.1. C#-Like? Close, but Not Completely
      2. 6.4.2. The Expression Builder
      3. 6.4.3. Syntax Basics
        1. 6.4.3.1. Equivalence Operator
        2. 6.4.3.2. String Concatenation
        3. 6.4.3.3. Line Continuation
        4. 6.4.3.4. Literals
          1. 6.4.3.4.1. Numeric Literals
          2. 6.4.3.4.2. String Literals
          3. 6.4.3.4.3. Boolean Literals
        5. 6.4.3.5. Referencing Variables
        6. 6.4.3.6. Referencing Columns
        7. 6.4.3.7. Boolean Expressions
        8. 6.4.3.8. Dealing with NULLs
          1. 6.4.3.8.1. NULLs and Variables
          2. 6.4.3.8.2. NULLs in Data Flow
        9. 6.4.3.9. String Functions
        10. 6.4.3.10. Conditional Expressions
        11. 6.4.3.11. Date Time Functions
      4. 6.4.4. Using Expressions in SSIS Packages
        1. 6.4.4.1. Using Variables as Expressions
        2. 6.4.4.2. Using Expressions in Properties of Connections
        3. 6.4.4.3. Using Expressions in Control Flow Tasks
        4. 6.4.4.4. Using Expressions in Control Flow Precedence
        5. 6.4.4.5. Using Expressions in Data Flow
    5. 6.5. Summary
  12. 7. Joining Data
    1. 7.1. The Lookup Component
    2. 7.2. The Merge Join Component
    3. 7.3. Contrasting to the Relational Join
    4. 7.4. New Lookup Features
    5. 7.5. Building the Basic Package
      1. 7.5.1. Creating the Basic Package
      2. 7.5.2. Using a Relational Join in the Source
      3. 7.5.3. Using the Merge Join Component
    6. 7.6. Using the Lookup Component
      1. 7.6.1. Full-Cache Mode
      2. 7.6.2. No-Cache Mode
      3. 7.6.3. Partial-Cache Mode
      4. 7.6.4. Multiple Outputs
      5. 7.6.5. Expressionable Properties
      6. 7.6.6. Cascaded Lookup Operations
    7. 7.7. Cache Connection Manager and Transform
    8. 7.8. Summary
  13. 8. Creating an End-to-End Package
    1. 8.1. Basic Transformation Tutorial
      1. 8.1.1. Creating Connections
      2. 8.1.2. Creating the Tasks
      3. 8.1.3. Creating the Data Flow
      4. 8.1.4. Completing the Package
      5. 8.1.5. Saving the Package
      6. 8.1.6. Executing the Package
    2. 8.2. Typical Mainframe ETL with Data Scrubbing
      1. 8.2.1. Creating the Data Flow
      2. 8.2.2. Handling Dirty Data
        1. 8.2.2.1. The Conditional Split Transformation
        2. 8.2.2.2. The Lookup Transformation
        3. 8.2.2.3. The Union All Transformation
      3. 8.2.3. Finalizing
      4. 8.2.4. Handling More Bad Data
    3. 8.3. Looping and the Dynamic Task
      1. 8.3.1. Looping
      2. 8.3.2. Making the Package Dynamic
    4. 8.4. Summary
  14. 9. Scripting in SSIS
    1. 9.1. Scripting?
    2. 9.2. Getting Started in SSIS Scripting
      1. 9.2.1. Selecting the Scripting Language
      2. 9.2.2. Using the VSTA Scripting IDE
      3. 9.2.3. Example: Hello World
      4. 9.2.4. Adding Code and Classes
      5. 9.2.5. Using Managed Assemblies
        1. 9.2.5.1. Example: Using Custom .NET Assemblies
    3. 9.3. Using the Script Task
      1. 9.3.1. Configuring the Script Task Editor
      2. 9.3.2. The Script Task Dts Object
      3. 9.3.3. Accessing Variables in the Script Task
        1. 9.3.3.1. Example: Using Script Task Variables to Control Package Flow
      4. 9.3.4. Connecting to Data Sources in a Script Task
        1. 9.3.4.1. Example: Retrieving Data into Variables from a Database
        2. 9.3.4.2. Example: Saving Data to an XML File
        3. 9.3.4.3. Example: Serializing Data to XML
      5. 9.3.5. Raising an Event in a Script Task
        1. 9.3.5.1. Example: Raise Some Events
        2. 9.3.5.2. Example: Respond to an Event
        3. 9.3.5.3. Example: Logging Event Information
      6. 9.3.6. Writing a Log Entry in a Script Task
        1. 9.3.6.1. Example: Script a Log Entry
    4. 9.4. Using the Script Component
      1. 9.4.1. Differences from a Script Task
      2. 9.4.2. Configuring the Script Component Editor
      3. 9.4.3. Accessing Variables in a Script Component
      4. 9.4.4. Connecting to Data Sources in a Script Component
      5. 9.4.5. Raising Events
      6. 9.4.6. Logging
        1. 9.4.6.1. Example: Script a Log Entry
      7. 9.4.7. Example: Data Validation
    5. 9.5. Essential Coding, Debugging, and Troubleshooting Techniques
      1. 9.5.1. Structured Exception Handling
      2. 9.5.2. Script Debugging and Troubleshooting
        1. 9.5.2.1. Breakpoints
        2. 9.5.2.2. Row Count and Data Viewers
        3. 9.5.2.3. Autos, Locals, and Watches
        4. 9.5.2.4. The Immediate Window
    6. 9.6. Summary
  15. 10. Loading a Data Warehouse
    1. 10.1. Data Profiling
      1. 10.1.1. Initial Execution of the Data Profiling Task
      2. 10.1.2. Reviewing the Results of the Data Profiling Task
      3. 10.1.3. Turning Data Profile Results into Actionable ETL Steps
    2. 10.2. Data Extraction
    3. 10.3. Dimension Table Loading
      1. 10.3.1. Loading a Simple Dimension Table
      2. 10.3.2. Loading a Complex Dimension Table
        1. 10.3.2.1. Preparing the Data
        2. 10.3.2.2. Handling Complicated Dimension Changes with the SCD Transformation
      3. 10.3.3. Considerations and Alternates to the SCD Transformation
    4. 10.4. Fact Table Loading
    5. 10.5. SSAS Processing
    6. 10.6. Master ETL Package
    7. 10.7. Summary
  16. 11. Using the Relational Engine
    1. 11.1. Data Extraction
      1. 11.1.1. SELECT * Is Bad
      2. 11.1.2. WHERE Is Your Friend
      3. 11.1.3. Transform during Extract
      4. 11.1.4. Many ANDs Make Light Work
      5. 11.1.5. SORT in the Database
      6. 11.1.6. Modularize
      7. 11.1.7. SQL Server Does Text Files Too
      8. 11.1.8. Use Set-Based Logic
    2. 11.2. SQL Server 2008 Change Data Capture
      1. 11.2.1. Benefits of SQL Server 2008 CDC
      2. 11.2.2. Preparation
      3. 11.2.3. Capture Instance Tables
      4. 11.2.4. The CDC API
      5. 11.2.5. Using CDC from within SSIS
    3. 11.3. Data Loading
      1. 11.3.1. Database Snapshots
      2. 11.3.2. The MERGE Operator
    4. 11.4. Summary
  17. 12. Accessing Heterogeneous Data
    1. 12.1. Excel and Access
      1. 12.1.1. Limited 64-Bit Support
      2. 12.1.2. Working with Excel Files
      3. 12.1.3. Access
        1. 12.1.3.1. Understanding Access Security
        2. 12.1.3.2. Configuring an Access Connection Manager
        3. 12.1.3.3. Importing from Access
        4. 12.1.3.4. Using a Parameter
    2. 12.2. Oracle
      1. 12.2.1. Oracle Client Setup
        1. 12.2.1.1. Installing the Oracle Client Software
        2. 12.2.1.2. Configuring the Oracle Client Software
        3. 12.2.1.3. 64-Bit Considerations
      2. 12.2.2. Importing Oracle Data
    3. 12.3. XML and Web Services
      1. 12.3.1. Configuring the Web Service Task
        1. 12.3.1.1. Weather by ZIP Code Example
        2. 12.3.1.2. The Currency Conversion Example
      2. 12.3.2. Working with XML Data as a Source
    4. 12.4. Flat Files
      1. 12.4.1. Loading Flat Files
      2. 12.4.2. Extracting Data from Flat Files
    5. 12.5. ODBC
    6. 12.6. Other Heterogeneous Sources
    7. 12.7. Summary
  18. 13. Reliability and Scalability
    1. 13.1. Restarting Packages
      1. 13.1.1. Simple Control Flow
      2. 13.1.2. Containers within Containers and Checkpoints
      3. 13.1.3. Variations on a Theme
        1. 13.1.3.1. Failing the Parent, Not the Package
        2. 13.1.3.2. Failing the Parent and the Package
        3. 13.1.3.3. Failing the Task with No Transaction
        4. 13.1.3.4. Failing the Package, Not the Sequence
      4. 13.1.4. Inside the Checkpoint File
    2. 13.2. Package Transactions
      1. 13.2.1. Single Package, Single Transaction
      2. 13.2.2. Single Package, Multiple Transactions
      3. 13.2.3. Two Packages, One Transaction
      4. 13.2.4. Single Package Using a Native Transaction in SQL Server
    3. 13.3. Error Outputs
    4. 13.4. Scaling Out
      1. 13.4.1. Architectural Improvements
        1. 13.4.1.1. Lookup Transformation Improvements
        2. 13.4.1.2. Data Pipeline Improvements
      2. 13.4.2. Scale Out Memory Pressures
      3. 13.4.3. Scale Out by Staging Data
        1. 13.4.3.1. Data Flow Restart
        2. 13.4.3.2. Scale across Machines
    5. 13.5. Summary
  19. 14. Understanding and Tuning the Data Flow Engine
    1. 14.1. The SSIS Engine
      1. 14.1.1. Understanding the SSIS Data Flow and Control Flow
        1. 14.1.1.1. Comparing and Contrasting the Data Flow and Control Flow
        2. 14.1.1.2. SSIS Package Execution Times from Package Start to Package Finish
      2. 14.1.2. Handling Workflows with the Control Flow
      3. 14.1.3. Data Processing in the Data Flow
      4. 14.1.4. Memory Buffer Architecture
      5. 14.1.5. Types of Transformations
        1. 14.1.5.1. Non-Blocking, Semi-Blocking, and Blocking
          1. 14.1.5.1.1. Non-Blocking Transformations, Streaming and Row-Based
          2. 14.1.5.1.2. Semi-Blocking Transformations
          3. 14.1.5.1.3. Blocking Transformations
        2. 14.1.5.2. Synchronous and Asynchronous Transformation Outputs
          1. 14.1.5.2.1. Asynchronous Transformation Outputs
          2. 14.1.5.2.2. Synchronous Transformation Outputs
        3. 14.1.5.3. Source and Destination Adapters
      6. 14.1.6. Advanced Data Flow Execution Concepts
        1. 14.1.6.1. Execution Trees
        2. 14.1.6.2. Monitoring Data Flow Execution
          1. 14.1.6.2.1. Pipeline Execution Tree Log Details
          2. 14.1.6.2.2. Pipeline Execution Plan Log Details
    2. 14.2. SSIS Data Flow Design and Tuning
      1. 14.2.1. Data Flow Design Practices
        1. 14.2.1.1. Leveraging the Data Flow
        2. 14.2.1.2. Data Integration and Correlation
        3. 14.2.1.3. Data Cleansing and Transformation
        4. 14.2.1.4. Staging Environments
      2. 14.2.2. Optimizing Package Processing
        1. 14.2.2.1. Optimizing Buffers, Execution Trees, and Engine Threads
        2. 14.2.2.2. Careful Use of Row-Based Transformations
        3. 14.2.2.3. Understand Blocking Transformation Impacts
      3. 14.2.3. Troubleshooting Data Flow Performance Bottlenecks
    3. 14.3. Pipeline Performance Monitoring
    4. 14.4. Summary
  20. 15. Source Control and Software Development Life Cycle
    1. 15.1. Introduction to Software Development Life Cycles
      1. 15.1.1. Software Development Life Cycles: A Brief History
      2. 15.1.2. Types of Software Development Life Cycles
        1. 15.1.2.1. Waterfall SDLCs
        2. 15.1.2.2. Iterative SDLCs
    2. 15.2. Versioning and Source Code Control
      1. 15.2.1. Microsoft Visual SourceSafe
      2. 15.2.2. Team Foundation Server, Team System, and SSIS
      3. 15.2.3. MSF Agile and SSIS
        1. 15.2.3.1. Work Items
        2. 15.2.3.2. Documents
        3. 15.2.3.3. Reports
      4. 15.2.4. The Project Portal
      5. 15.2.5. Putting It to Work
        1. 15.2.5.1. Version and Source Control with Team System
        2. 15.2.5.2. Shelving and Unshelving
        3. 15.2.5.3. Branching
        4. 15.2.5.4. Merging
        5. 15.2.5.5. Labeling (Striping) Source Versions
    3. 15.3. Code Deployment and Promotion from Development to Test to Production
      1. 15.3.1. The Deployment Wizard
      2. 15.3.2. Import a Package
    4. 15.4. Summary
  21. 16. DTS 2000 Migration
    1. 16.1. Managing DTS 2000 Packages within SQL Server Management Studio
    2. 16.2. Running DTS 2000 Packages under SSIS
    3. 16.3. Migrating DTS 2000 Packages to SSIS
    4. 16.4. Using the Package Migration Wizard
    5. 16.5. Third-Party Migration Solution
    6. 16.6. Summary
  22. 17. Error and Event Handling
    1. 17.1. Precedence Constraint
      1. 17.1.1. Precedence Constraint Basics
      2. 17.1.2. Advanced Precedence Constraints and Expressions
        1. 17.1.2.1. Using Boolean Expressions with Precedence Constraints
        2. 17.1.2.2. Working with Multiple Precedence Constraints
    2. 17.2. Event Handling
      1. 17.2.1. Events
      2. 17.2.2. Inventory Example
        1. 17.2.2.1. Using the OnError Event Handler Event
        2. 17.2.2.2. Using the OnPreExecute Event Handler Event
      3. 17.2.3. Event Handler Inheritance
    3. 17.3. Breakpoints
    4. 17.4. Error Rows
    5. 17.5. Logging
      1. 17.5.1. Logging Providers
      2. 17.5.2. Log Events
    6. 17.6. Summary
  23. 18. Programming and Extending SSIS
    1. 18.1. The Sample Components
      1. 18.1.1. Component 1: Source Adapter
      2. 18.1.2. Component 2: Transformation
      3. 18.1.3. Component 3: Destination Adapter
    2. 18.2. The Pipeline Component Methods
      1. 18.2.1. Design-Time Functionality
        1. 18.2.1.1. ProvideComponentProperties
        2. 18.2.1.2. Validate
        3. 18.2.1.3. ReinitializeMetaData
        4. 18.2.1.4. MapInputColumn and MapOutputColumn
        5. 18.2.1.5. Input and Output Verification Methods
        6. 18.2.1.6. Set Column Data Types
        7. 18.2.1.7. PerformUpgrade
        8. 18.2.1.8. RegisterEvents
        9. 18.2.1.9. RegisterLogEntries
        10. 18.2.1.10. SetComponentProperty
        11. 18.2.1.11. Set Column Properties
        12. 18.2.1.12. SetUsageType
        13. 18.2.1.13. On Path Attachment
      2. 18.2.2. Runtime
        1. 18.2.2.1. PrepareForExecute
        2. 18.2.2.2. PreExecute
        3. 18.2.2.3. PrimeOutput and ProcessInput
        4. 18.2.2.4. PostExecute
        5. 18.2.2.5. Cleanup
        6. 18.2.2.6. DescribeRedirectedErrorCode
      3. 18.2.3. Connection Time
        1. 18.2.3.1. AcquireConnections
        2. 18.2.3.2. ReleaseConnections
    3. 18.3. Building the Components
      1. 18.3.1. Preparation
      2. 18.3.2. Building the Source Component
      3. 18.3.3. Building the Transform Component
      4. 18.3.4. Building the Destination Adapter
    4. 18.4. Using the Components
      1. 18.4.1. Installing the Components
      2. 18.4.2. Debugging Components
      3. 18.4.3. Design-Time
      4. 18.4.4. Building the Complete Package
      5. 18.4.5. Runtime Debugging
    5. 18.5. Upgrading to SQL 2008
    6. 18.6. Summary
  24. 19. Adding a User Interface to Your Component
    1. 19.1. Three Key Steps
    2. 19.2. Building the User Interface
      1. 19.2.1. Adding the Project
      2. 19.2.2. Implementing IDtsComponentUI
        1. 19.2.2.1. IDtsComponentUI.Delete
        2. 19.2.2.2. IDtsComponentUI.Help
        3. 19.2.2.3. DtsComponentUI.New
        4. 19.2.2.4. IDtsComponentUI.Initialize
        5. 19.2.2.5. IDtsComponentUI.Edit
      3. 19.2.3. Setting the UITypeName
      4. 19.2.4. Building the Form
        1. 19.2.4.1. Form Constructor
        2. 19.2.4.2. Column Display
        3. 19.2.4.3. Column Selection
    3. 19.3. Further Development
      1. 19.3.1. Runtime Connections
      2. 19.3.2. Component Properties
      3. 19.3.3. Handling Errors and Warnings
      4. 19.3.4. Column Properties
    4. 19.4. Other Considerations
    5. 19.5. Summary
  25. 20. External Management and WMI Task Implementation
    1. 20.1. External Management of SSIS with Managed Code
      1. 20.1.1. Setting Up a Test SSIS Package for Demonstration Purposes
      2. 20.1.2. The DTS Runtime Managed Code Library
    2. 20.2. Application Object Maintenance Operations
      1. 20.2.1. Package Maintenance Operations
        1. 20.2.1.1. A Package Maintenance Example
        2. 20.2.1.2. A Package Transfer Example
      2. 20.2.2. Package Folder Maintenance
      3. 20.2.3. Package Role Maintenance
      4. 20.2.4. Package Monitoring
      5. 20.2.5. Package Listing
      6. 20.2.6. A Package Management Example
    3. 20.3. Package Log Providers
      1. 20.3.1. Specifying Events to Log
      2. 20.3.2. Programming to Log Providers
    4. 20.4. Package Configurations
      1. 20.4.1. Creating a Configuration
      2. 20.4.2. Programming the Configuration Object
      3. 20.4.3. Configuration Object
    5. 20.5. Windows Management Instrumentation Tasks
      1. 20.5.1. WMI Reader Task Explained
      2. 20.5.2. WMI Data Reader Example
      3. 20.5.3. WMI Event Watcher Task
      4. 20.5.4. WMI Event Watcher Task Example
    6. 20.6. Summary
  26. 21. Using SSIS with External Applications
    1. 21.1. InfoPath Documents
    2. 21.2. ASP.NET Applications
    3. 21.3. Winform .NET Applications
    4. 21.4. Summary
  27. 22. Administering SSIS
    1. 22.1. Package Configuration
    2. 22.2. Deployment Utility
      1. 22.2.1. Creating the Deployment Manifest
      2. 22.2.2. The Package Deployment Wizard
    3. 22.3. The Package Store
      1. 22.3.1. Creating a Central SSIS Server
      2. 22.3.2. Clustering SSIS
      3. 22.3.3. File System or the MSDB Deployment
    4. 22.4. Management Studio
    5. 22.5. Running Packages with DTExecUI
    6. 22.6. Security
    7. 22.7. Command-Line Utilities
      1. 22.7.1. DTExec
      2. 22.7.2. DTUtil
        1. 22.7.2.1. Re-Encrypting All Packages in a Directory
        2. 22.7.2.2. Handling a Corrupt Package
    8. 22.8. Scheduling a Package
    9. 22.9. Proxy Accounts
    10. 22.10. 64-Bit Issues
    11. 22.11. Performance Counters
    12. 22.12. Summary
  28. 23. Case Study: A Programmatic Example
    1. 23.1. What You Will Take Away
    2. 23.2. Background
    3. 23.3. Business Problem
    4. 23.4. Solution Summary
    5. 23.5. Solution Architecture
      1. 23.5.1. Naming Conventions and Tips
      2. 23.5.2. Additional SSIS Tips Before You Start a Large Project
    6. 23.6. Data Architecture
      1. 23.6.1. File Storage Location Setup
      2. 23.6.2. Bank ACH Payments
      3. 23.6.3. Lockbox Files
      4. 23.6.4. PayPal or Direct Credits to Corporate Account
      5. 23.6.5. Case Study Database Model
      6. 23.6.6. Database Setup
        1. 23.6.6.1. Customer
        2. 23.6.6.2. Invoice
        3. 23.6.6.3. CustomerLookUp
        4. 23.6.6.4. BankBatch
        5. 23.6.6.5. BankBatchDetail
        6. 23.6.6.6. Corporate Ledger Data
        7. 23.6.6.7. ErrorDetail
        8. 23.6.6.8. Stored Procedure to Add Batches
        9. 23.6.6.9. Stored Procedure to Update a Batch with Invoice and Customer Id
        10. 23.6.6.10. Stored Procedure to Balance a Batch
    7. 23.7. Case Study Load Packages
      1. 23.7.1. Bank File Load Package
        1. 23.7.1.1. Bank File Package and Variable Setup Tasks
        2. 23.7.1.2. Bank File Control Flow Processing
          1. 23.7.1.2.1. Bank File Control Flow File Loop
          2. 23.7.1.2.2. Bank File Control Flow Retrieval of File Properties
          3. 23.7.1.2.3. Bank File Control Flow Batch Creation
        3. 23.7.1.3. Bank File Data Flow Validation
          1. 23.7.1.3.1. Bank File Data Flow Parsing and Error Handling
          2. 23.7.1.3.2. Bank File Data Flow Validation
          3. 23.7.1.3.3. Bank File Data Flow Capturing Total Batch Items
        4. 23.7.1.4. Bank File Data Flow Detail Processing ETL
          1. 23.7.1.4.1. Processing the Bank File Check and Invoice Detail Lines
          2. 23.7.1.4.2. Saving a Bank File Snapshot in the Database
      2. 23.7.2. ACH Load Package
        1. 23.7.2.1. ACH Package Setup and File System Tasks
        2. 23.7.2.2. ACH Control Flow Processing
          1. 23.7.2.2.1. ACH Control Flow Loop
          2. 23.7.2.2.2. ACH Control Flow Retrieval of XML File Size
          3. 23.7.2.2.3. ACH Control Flow Batch Creation
        3. 23.7.2.3. ACH Data Flow Validation
          1. 23.7.2.3.1. ACH Data Flow Parsing and Error Handling
          2. 23.7.2.3.2. ACH Data Flow Validation
          3. 23.7.2.3.3. ACH Data Flow Capturing Total Batch Items
        4. 23.7.2.4. ACH Data Flow Detail Processing ETL
          1. 23.7.2.4.1. Processing the ACH File
          2. 23.7.2.4.2. Saving the ACH File Snapshot in the Database
      3. 23.7.3. Email Load Package
        1. 23.7.3.1. Email Package Setup and File System Tasks
        2. 23.7.3.2. Email Control Flow Processing
        3. 23.7.3.3. Email Data Flow Processing
      4. 23.7.4. Testing
    8. 23.8. Case Study Invoice Matching Process
      1. 23.8.1. Matching Process Control Flow
        1. 23.8.1.1. Matching Process Package Setup
        2. 23.8.1.2. Add the Matching Process Logic
      2. 23.8.2. Matching Process High-Confidence Data Flow
      3. 23.8.3. Matching Process Medium-Confidence Data Flow
      4. 23.8.4. Interpreting the Results
    9. 23.9. Creating a Parent Driver Package
      1. 23.9.1. Driver Package Setup
      2. 23.9.2. Driver Package Deployment
    10. 23.10. Summary

Product information

  • Title: Professional SQL Server® 2008 Integration Services
  • Author(s): Brian Knight, Erik Veerman, Grant Dickinson, Douglas Hinson, Darren Herbold
  • Release date: October 2008
  • Publisher(s): Wrox
  • ISBN: 9780470247952