Microsoft® SQL Server™ 2005 Integration Services Step by Step

Microsoft® SQL Server™ 2005 Integration Services Step by Step

by Hitachi Consulting Paul Turley Joe Kaspizak, Scott Cameron, Satoshi Iizuka, and Pablo Guzman
Released June 2007
Publisher(s): Microsoft Press
ISBN: 9780735624054

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

Teach yourself the fundamentals of SQL Server Integration Services—one step at a time. This practical, learn-by-doing tutorial delivers the guidance you need to transform and consolidate data—and build solutions that support your business intelligence needs.

Discover how to:

  • Design and execute packages that transform data between files and relational databases

  • Configure connection managers to access other data sources

  • Create data flows that alter, split, match, and merge data

  • Develop event-handlers and monitor package performance

  • Encrypt sensitive information and control access to packages

  • Customize script code to automate Control Flow and Data Flow tasks

  • Debug, troubleshoot, and optimize packages

    • CD features:

  • All practice exercises

  • Sample databases

  • Fully searchable eBook

    • For customers who purchase an ebook version of this title, instructions for downloading the CD files can be found in the ebook.

    Table of contents

    1. Introduction
      1. Finding Your Best Starting Point
      2. About the Companion CD-ROM
      3. System Requirements
      4. Installing and Using the Sample Files
      5. Conventions and Features in This Book
    2. I. Getting Started with Integration Services
      1. 1. Introduction to SQL Server Integration Services
        1. Common SSIS Applications
        2. SSIS Objects and Process Control Components
        3. SSIS Process Control
          1. SSIS Control Flow
          2. SSIS Data Flow
          3. SSIS Data Pipeline
          4. SSIS Event Handler
        4. SSIS Components
          1. SSIS Development Studio
          2. SSIS Runtime Services
          3. SSIS Package Deployment
        5. SQL Server 2000 DTS Migration
        6. Chapter 1 Quick Reference
      2. 2. Building Your First Package
        1. Exploring Business Intelligence Development Studio
          1. Solution Explorer
          2. Docking Utility Windows
          3. Exploring an SSIS Project in BIDS
            1. Open SSIS Sample Solution
            2. View the ScrapReason data source view in the Data Source view designer
            3. View the CreateLists package in the package designer
            4. Open the Data Flow Task – Employee List
            5. Explore the connection managers
            6. Open the OLE DB Source – Employee Query task
            7. Open the Derived Column – FullName task
            8. Open the Sort – Department Shift Employee task
            9. Open the Flat File Destination – Employees CSV task
            10. View the final output
            11. Execute the CreateLists package
            12. View Employees, ScrappedProducts, and customer records files in the data folder
            13. Review the ImportCustomers package
            14. Excute the ImportCustomers package
        2. Using the SSIS Import and Export Wizard
          1. Creating Tables in a New Database
            1. Run the wizard in BIDS
            2. Review the is2005sbs database by using Management Studio
            3. Create the QuickStart solution to contain the QuickStartIS SSIS project
            4. Import tables into a new QuickStartODS database with a new package
        3. Reviewing Package Elements
          1. Reviewing a Package Created Using the Import and Export Wizard
            1. Review the preparation SQL task
            2. Review the data flow task
        4. Testing a Package
          1. Executing the Package in the Designer
            1. Execute the QuickStartIS.dtsx package
            2. Stop debugging and close the package designer
            3. Review the QuickStartODS database tables by using SSMS
        5. Chapter 2 Quick Reference
    3. II. Designing Packages
      1. 3. Extracting and Loading Data
        1. Connection Managers
          1. Connection Manager Types
          2. Creating a New Integration Services Project
            1. Create a new Integration Services project
          3. Adding Connection Managers
            1. Add an OLE DB connection manager for the is2005sbs database
            2. Add an Office Excel connection manager to the Employee.xls file
          4. Creating a Data Flow
            1. Create a data flow task
          5. Adding Data Adapters
            1. Add an OLE DB source data adapter
            2. Add the localhost.is2005sbs Connection Manager to the OLE DB Source data adapter
            3. Map the connection manager to the data adapter
            4. Add an Excel Destination data adapter
            5. Add the Employee connection manager to the Excel Destination data adapter
          6. Executing the Package
            1. Execute the package
        2. Using Data Sources and Data Source Views
          1. Creating a Data Source
            1. Create a data source
          2. Creating a Data Source View
            1. Create a data source view
          3. Creating a New Named Query
            1. Create a named query
          4. Copying Data from a Named Query to a Flat File
            1. Copy data from a named query products table to a flat file
            2. Connect to a flat file destination
          5. Executing the Package
            1. Stop Debugging
        3. Chapter 3 Quick Reference
      2. 4. Using Data Flow Transformations
        1. Creating Data Flow in a Package
          1. Data Flow Sources
          2. Data Flow Transformations
          3. Data Flow Destinations
          4. Data Source Connections
        2. SSIS Transformations
          1. Row Transformations
          2. Rowset Transformations
          3. Split and Join Transformations
          4. Data Quality Transformations
          5. Data-Mining Transformations
          6. Other Transformations
          7. Synchronous and Asynchronous Transformations
        3. Using Expressions in Packages
          1. Expression Usage in SSIS
            1. Conditional Split Transformation
            2. Derived Column Transformation
            3. Variables
            4. Precedence Constraints
            5. The For Loop Container
          2. Expressions Elements
          3. Building Expressions
        4. Using Data Flow Transformations
          1. Opening and Exploring the SSIS Project
            1. Open and explore the SSIS project
            2. Preview the NewProducts.txt file
          2. Creating the Data Flow Task
            1. Create the Data Flow task
          3. Using a Flat File Source
            1. Configuring a Flat File Source
              1. Create a data adapter
          4. Adding a Connection Manager
            1. Add a connection manager
          5. Adding a Conditional Split Transformation
            1. Add a Conditional Split transformation
          6. Adding a Derived Column Transformation
            1. Add a Derived Column transformation
          7. Viewing the Properties of the Derived Column Transformation
            1. View the properties of the Derived Column transformation
          8. Adding a Flat File Destination Data Adapter and Executing the Package
            1. Add a flat-file destination data adapter
            2. Execute the package
          9. Sending Output to Different Destinations
            1. Add a Multicast transformation
            2. Add a SQL Server destination data adapter
            3. Create the task that deletes data from the table
            4. Execute the package and check the results
        5. Configuring Error Output
          1. Types of Errors
          2. Error Options
          3. Exploring the LookupGeography Package
            1. Open and explore the package
          4. Creating a Task
            1. Create the Data Flow Task – Lookup Geography task
          5. Creating and Naming a Flat File Source
            1. Create a flat file
          6. Adding a Data Conversion Transformation
            1. Convert the GeographyKey column to an integer
          7. Adding a Lookup Transformation
            1. Add a Lookup transformation
          8. Adding a Flat File Destination for Lookup Errors
            1. Write lookup errors to a file
          9. Adding a Flat File Destination for Successful Lookups
            1. Write successful rows to another flat file
          10. Executing the Package and Checking the Results
            1. Execute the package and check the results
        6. Chapter 4 Quick Reference
      3. 5. Managing Control Flow
        1. Control Flow Elements
          1. Control Flow Components
            1. Work with tasks and DelayValidation
          2. Using Containers
            1. Add sequence containers
            2. Test whether the output file is found
            3. Assign a value to the variable
            4. Create a placeholder
          3. Adding a Fuzzy Lookup Transformation
            1. Add a Fuzzy Lookup transformation
            2. Add a SQL Server destination
            3. Add an Execute SQL task
          4. Adding a Foreach Loop Container
            1. Add a Foreach Loop container to Sequence Container – File Exists
          5. Applying Precedence Constraints
            1. Apply precedence constraints
        2. Chapter 5 Quick Reference
      4. 6. Scripting Tasks
        1. Understanding Scripting Tasks
        2. Implementing a Script Task
          1. Creating a New Script Task and Initiating Code
            1. Create a new project and add a Script task
          2. Handling Errors
            1. Add error-handling code in the Script Task
          3. Providing a Message to the Progress Tab
            1. Modify the script to fire an event
          4. Providing Verbose Information to the Log File
            1. Configure package log settings
          5. Using Variables
            1. Configure variable settings
            2. Modify the script to read variables
          6. Modifying a Variable at Run Time
            1. Add a new Script task and update variables at run time
        3. Understanding the Script Component
        4. Implementing the Script Component
          1. Reviewing a Sample Project
            1. Implement validation using the Transformation script component
        5. Understanding an ActiveX Script Task
          1. Implementing an ActiveX Script Task
            1. Create a new package with a single ActiveX Script task
        6. Chapter 6 Quick Reference
      5. 7. Debugging Packages
        1. Debugging Control Flow
          1. Understanding Breakpoints
            1. Set breakpoints and suspend package execution
          2. Reviewing Debug Windows
            1. Call Stack
            2. Breakpoints
              1. Execute package and review debug windows
          3. Understanding Progress Messages
          4. Executing a Package Partially
            1. Disable a task and execute a package partially
        2. Debugging Data Flow
          1. Browsing Data By Using Data Viewers
            1. Set data viewers and browse data
          2. Understanding Other Options
        3. Debugging Script Task
          1. Walk Through Code by Using Breakpoints
            1. Set breakpoints and walk through code
          2. Reviewing State by Using VSA Features
            1. Review state, using debug windows
        4. Chapter 7 Quick Reference
      6. 8. Managing Package Execution
        1. Understanding Package Configurations
          1. Configuration Benefits
          2. Configuration Types
          3. Understanding the XML Configuration File
          4. Specifying a New XML Configuration File Location
        2. Creating and Editing an XML Configuration File
          1. Opening the SSIS Project and Executing the Package
            1. Create a new SSIS project
          2. Creating an XML Configuration File
            1. Create an XML configuration file
          3. Editing the XML Configuration File
            1. Edit the XML configuration file
          4. Testing the Package with the New Configuration
            1. Test the package
        3. Multiple Configuration Files
          1. Environment Variable
          2. Registry Entry
          3. Parent Package Variables
          4. SQL Server Tables
          5. Direct and Indirect Configurations
        4. Using Configuration Files
          1. Determining Configuration Order
          2. Evaluating Configuration Failure
          3. Using Multiple Configurations
        5. Creating Multiple Configuration Files
          1. Creating the Database and the OLE DB Connection Manager
            1. Create a new database
          2. Creating the Environment Variable
            1. Create a database environment
          3. Creating the Environment Variable Configuration
            1. Configure the environment variable
          4. Creating the SQL Server Configuration
            1. Create a SQL Server table
          5. Testing the Package with the New Configuration
            1. Test the new configuration
          6. Exploring the Parent Package
            1. View the ParentPackage.dtsx package
          7. Creating the Parent Package Variable Configuration
            1. Create the ParentProductsDestination parent package variable configuration
            2. Test the package with the new configuration
        6. Exploring Package Execution Options
          1. Using the SQL Server Import and Export Wizard to Execute Packages
            1. Start the SQL Server Wizard from BIDS
            2. Start the SQL Server Wizard from Management Studio
          2. Using DTExecUI to Execute Packages
            1. Run a package by using the DTExecUI utility
          3. Using DTExec to Execute Packages
            1. Run a package by using the DTExec utility
          4. Using SQL Server Management Studio to Execute a Package
            1. Run a package by using SQL Server Management Studio
          5. Extending Package Execution Options
            1. Execute Package tasks
            2. Execute Process tasks
          6. Using SQL Server Agent
            1. Execute tasks and containers and then disable the task and execute the package
          7. Using the Execute Package Utility
            1. Use the Execute Package Utility
            2. Use the command-line utility
            3. Executing a package by using SQL Agent
        7. Understanding Package Logging
        8. Implementing Package Logging
          1. Configuring Package Logging
            1. Configure package logging
          2. Executing the Package and Viewing the Logs
            1. Execute the package and view the logs
            2. Configure container and task logging
            3. Execute the package and view the logs
        9. Chapter 8 Quick Reference
    4. III. Managing Packages
      1. 9. Detecting and Handling Processing Errors
        1. Basic Error Detection and Handling
          1. Understanding Metadata Lineage
          2. Understanding Validation
          3. Understanding Precedence Constraints
          4. Understanding Data Flow Transformations
            1. Configuring a Transformation to Fail When an Error Occurs
            2. Configuring a Transformation to Re-route Error-Causing Records
            3. Configuring a Data Flow Transformation to Ignore Errors
        2. Understanding Event Handlers
          1. Using Event Handlers to Perform Tasks
          2. Triggering an Event Handler
          3. Using the Event Handlers Provided by SSIS
        3. Creating Event Handlers
          1. Determine whether the QuickStartODS database is on your computer
          2. Create the QuickStartODS database
          3. Accessing the SSIS Design Environment
            1. Open the NewProducts.dtsx package
          4. Creating an OnPreExecute Event Handler
            1. Create an OnPreExecute event handler
          5. Adding a Task to an Event Handler
            1. Add an Execute SQL task to the OnPreExecute event handler
          6. Configuring the Task
            1. Configure the Execute SQL task
          7. Mapping SSIS Variables to SQL Statement Parameters
            1. Configure the Execute SQL task to run parameterized SQL statements
          8. Creating a Log Finish Event Handler
            1. Add a Log Finish Execute SQL task to an OnPostExecute event handler
          9. Creating a Log Error Event Handler
            1. Add a Log Error Execute SQL task to an OnError event handler
          10. Executing the Package
            1. Execute the NewProducts.dtsx package and view the results in Management Studio
          11. Testing the Package with Invalid Data
            1. Test the NewProducts.dtsx package with invalid data
          12. Creating an Event Handler to Fix the Problem
            1. Create an OnTaskFailed event handler for executable lookup names
          13. Creating a Task to Move the File with Invalid Data
            1. Create a File System task and a new connection manager
          14. Setting Connection Manager Settings
            1. Set connection manager settings
          15. Preventing Events from Escalating to Containers and Packages
            1. Prevent the OnTaskFailed event from escalating from the Lookup Names task to the Foreach Loop container
          16. Changing Error Count Properties
            1. Change the MaximumErrorCount property from 1 to 100
          17. Executing the Package
            1. Execute the NewProducts.dtsx package
        4. Maintaining Data Consistency with Transactions
          1. Configuring Transactions
        5. Using Checkpoint Restarts
          1. Understanding the Benefits of Checkpoints
          2. Configuring Packages for Checkpoints
        6. Using Checkpoints and Transactions
          1. Preparing to Use Checkpoints and Transactions to Fix the Error
            1. Open the PrepLoadDimProd.dtsx package and view the tables in Management Studio
          2. Becoming Familiar with the LoadDimProd Package
            1. Become familiar with the package
          3. Fixing the Error
            1. Fix the error
          4. Implementing Checkpoints
            1. Implement a checkpoint file to restart the package at the point of error
        7. Chapter 9 Quick Reference
      2. 10. Securing and Deploying SSIS Packages
        1. Creating a Deployment Utility
          1. Using the Package Installation Wizard
            1. Enable the deployment utility
            2. Build the SSIS Sample Project
            3. Connect to Integration Services
        2. Securing a Package
          1. Package Encryption
          2. Password Protection
          3. ProtectionLevel Property
        3. Role-Based Security
          1. Applying Security
            1. Change the protection level of a package to EncryptAllWithPassword
            2. Assign reader and writer roles to a package
        4. Deployment Options
          1. Push Deployment
          2. Pull Deployment
          3. Managing Packages on the SSIS Server
            1. Import the ImportCustomers package to the file system
            2. Import to MSDB
            3. Export a deployed package
            4. Execute the file system package
            5. Monitor running packages
        5. Creating and Applying a Configuration
          1. Adding a Configuration to the Project
            1. Use the SSIS BIDS menu to add a configuration
        6. Executing a Deployed Package
        7. Monitoring Package Execution and Event Logs
          1. Applying a Configuration
            1. Delete the existing package from SSMS
            2. Add a configuration to the ImportCustomers.dtsx package
            3. Deploy the package
            4. Start the Package Installation Wizard
            5. Inspect the alternate configuration files
            6. Run the deployed package
            7. Execute the package by using the configuration files
            8. Execute the package
        8. Chapter 10 Quick Reference
      3. 11. Optimizing SSIS Packages
        1. SSIS Engine Overview
          1. Runtime Engine
          2. Data Pipeline Engine
        2. Memory Buffer Architecture
          1. Buffer Usage
        3. Execution Trees
        4. Synchronous and Asynchronous Processing
        5. Data Blocking
          1. Blocking Transformations
          2. Partially Blocking Transformations
          3. Row Transformations
        6. Non-blocking Transformations
          1. Sources
        7. Buffer Settings
        8. Managing Parallelism
        9. Data Source Tuning
        10. Performance Management
          1. Loops
          2. Flat File Sources
          3. Filters and Variables
          4. Data Destination Management
            1. Locking Modes
            2. Drop or Disable Indexes
            3. Use Explicit Transactions
          5. Performance-Tuning Exercises
            1. Create a destination table
          6. Working with Buffer Properties
            1. Test execution performance
          7. Working with a SQL Server Destination
            1. Use a SQL Server connection manager
            2. Test DefaultBufferMaxRows and DefaultBufferSize
          8. Design Considerations
            1. Test a package with blocking transformations
        11. Performance Management
          1. Execution Trees
          2. Execution Plans
        12. Iterative Design Optimization
          1. Logging an Execution Plan
            1. View logging options and add an event handler
        13. SSIS Log Reports
        14. Chapter 11 Quick Reference
    5. IV. Applying SSIS to Data Warehousing
      1. 12. Data Warehouse Concepts
        1. Data Warehouse Objectives
        2. Data Warehouse Characteristics
          1. Providing Data for Business Analysis Processes
          2. Integrating Data from Heterogeneous Source Systems
          3. Combining Validated Source Data
          4. Organizing Data into Nonvolatile, Subject-Specific Groups
          5. Storing Data in Structures Optimized for Extraction and Queries
        3. Data Warehouse Fundamentals
        4. Business Intelligence Solution Goals
          1. Combining Relevant Data from Multiple Sources
          2. Providing Fast and Easy Access
        5. Focus on Decisions
        6. Data Granularity
          1. Supporting Business Decisions
        7. Update Frequency and Persistence
          1. Historical Data
        8. Changing Dimensions
        9. Surrogate Keys
        10. Additive Measures
        11. Reviewing an Operational and Database Schema
          1. Creating a Database Diagram
            1. Create a new diagram for the is2005sbs database
            2. Organize the diagram
            3. Save the diagram
            4. Query the is2005sbs database
        12. Data Warehouse System Components
          1. Fact and Dimension Tables
          2. Dimension Table Characteristics
            1. Star Schema Dimensions
            2. Snowflake Schema Dimensions
        13. Reviewing and Comparing a Data Warehouse Database Schema
          1. Creating a Database Diagram
            1. Add relationships to the database
            2. Create a new diagram for the is2005sbsDW database
            3. Save the diagram
            4. Query the is2005sbsDW database
            5. Reset the relationships for is2005sbsDW
        14. Data Warehouse in Summary
          1. Reset the Relationships for is2005sbsDW
        15. Chapter 12 Quick Reference
      2. 13. Populating Data Warehouse Structures
        1. Data Warehouse Characteristics
        2. Implementing Staging Tables
        3. Types of Staging Schemes
          1. Staging Data from Multiple Sources
          2. Staggered Staging
          3. Persisted Staging
          4. Accumulated Staging
          5. Chunked Accumulated Staging
          6. Other Destination Considerations
        4. Managing Dimension Tables Part 1
          1. Loading Dimension Tables by Using a Left Outer Join
            1. Create a new project for dimension table load packages
            2. Add tables to find new dimension members
            3. Configure a Left Outer Join Merge join task
            4. Add a conditional split to find new customers
            5. Add a flat file destination
        5. Managing Dimension Tables Part 2
          1. Loading Dimension Tables Part 2
            1. Add a new package
            2. Add a Lookup task process
            3. Add a flat file destination
        6. Slowly Changing Dimensions
          1. Managing Slowly Changing Dimensions
            1. Add a new package for designing a slowly changing dimension process
            2. Add a Percentage Sampling transformation
            3. Connect to SSMS to create a simple database for a new dimension table
            4. Create a new table within a SQL Server destination
            5. Initialize the CurrentRecord column for Slowly Changing Dimension reference
            6. Add a new control flow for customer dimension updates
            7. Add a Slowly Changing Dimension transformation
        7. Managing Fact Tables
          1. Aggregating Data in Fact Tables
          2. Loading Fact Tables
            1. Add a new package for fact table load processing
            2. Add a Multicast task and an Aggregate task
            3. Add a Derived Column task
            4. Add a Data Conversion task
            5. Add a new data flow to load the fact table from the staging table
            6. Add a variable and a Row Count task
            7. Add an OLE DB destination and configure Error Output
        8. Chapter 13 Quick Reference
      3. 14. SSIS General Principles
        1. Designing SSIS Packages
          1. OVAL Principles of SSIS Package Design
          2. Using SSIS Components in Your Design
            1. Using Variables
            2. Using the Lookup Task versus Merge Join
            3. Using Database Snapshots
            4. Designing for Performance and Maintenance
            5. Using Fast Parse
            6. Defining Your Best Practices
          3. Create a Master–Child Package
            1. Open project and build packages
            2. Add Execute Package tasks
            3. Add Data Flow tasks to the child packages
            4. Add a Row Count task and variable
            5. Disable an Execute Package task
          4. Organizing Package Components
            1. Using Prefixes to Identify SSIS Package Components
            2. Defining Project Folders
            3. Managing Performance and Debugging
            4. Managing Buffers and Memory
            5. Managing CPU Use
          5. Managing SSIS Application Deployment
            1. Designing for Deployment
            2. Using SSIS Package Configurations
            3. Managing Multiple Schemas with SSIS Package Design
            4. Logging Reports
        2. Chapter 14 Quick Reference
    6. A. About the Authors
      1. Paul Turley
      2. Joe Kasprzak
      3. Scott Cameron
      4. Satoshi Iizuka
      5. Pablo Guzman
      6. Supporting Author: Anne Bockman Hansen
    7. B. For Web Developers
      1. Also See
    8. C. Additional SQL Server Resources for Developers
    9. Index
    10. About the Authors
    11. Copyright

    Product information

    • Title: Microsoft® SQL Server™ 2005 Integration Services Step by Step
    • Author(s): Hitachi Consulting Paul Turley Joe Kaspizak, Scott Cameron, Satoshi Iizuka, and Pablo Guzman
    • Release date: June 2007
    • Publisher(s): Microsoft Press
    • ISBN: 9780735624054