Sign in  |  Register
Advanced search
Publication Cover

CITATION

Nanda, Ashwani. Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition. US: McGraw-Hill Osborne Media, 2010.

Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition

Authors: Ashwani Nanda

Published:  September 2010

eISBN: 9780071736411 0071736417 | ISBN: 9780071736404
Open eBook
  • Contents
  • Acknowledgments
  • Introduction
  • Chapter 1 Introducing SQL Server Integration Services
  • Integration Services: Features and Uses
  • Integration Services Architecture
  • Integration Services Designer and Management Tools
  • Data Warehousing Loading
  • Standardizing and Enhancing Data Quality Features
  • Converting Data into Meaningful Information
  • Data Consolidation
  • Package Security Features
  • Service-Oriented Architecture
  • SSIS Package as a Data Source
  • Programmability
  • Scripting
  • Easy Management of SSIS Packages
  • Automating Administrative Tasks
  • Easy Deployment Features
  • Legacy Support Features
  • What’s New in Integration Services 2008
  • Better Lookup
  • Improved ADO NET Components
  • Powerful Scripting
  • Extended Import and Export Wizard
  • Ability to Profile Your Data
  • Optimized Thread Allocation
  • SSIS Package Upgrade Wizard
  • Taking Advantage of Change Data Capture
  • Benefiting from T-SQL Merge Statement
  • Enhanced Debugging
  • Inclusion of New Date and Time Data Types
  • Where Is DTS in SQL Server 2008?
  • Integration Services in SQL Server 2008 Editions
  • 32-Bit Editions vs 64-Bit Editions
  • Integration Services Architecture
  • Integration Services Service
  • Integration Services Object Model
  • Integration Services Run Time
  • Integration Services Data Flow
  • Installing Integration Services
  • Installing Integration Services on a Clean System
  • Hands-On: Installing SQL Server 2008 Integration Services
  • Installing Integration Services from the Command Prompt
  • Installing Side by Side
  • Upgrading to SQL Server 2008 Integration Services
  • Business Intelligence Development Studio
  • Hands-On: Creating a Blank Integration Services Project
  • SQL Server Management Studio
  • Hands-On: Connecting to Integration Services Service
  • Summary
  • Chapter 2 Getting Started with Wizards
  • Starting SQL Server Import and Export Wizard
  • Hands-On: Importing a Flat File into SQL Server 2008
  • Using Business Intelligence Development Studio
  • Hands-On: Exploring an SQL Server Import and Export Wizard Package Using BIDS
  • Integration Services Connections Project Wizard
  • Analyzing Data Quality with the Data Profiling Task
  • Single-Column Profiles
  • Multiple-Column Profiles
  • Hands-On: Using Data Profiling Task
  • Summary
  • Chapter 3 Nuts and Bolts of the SSIS Workflow
  • Integration Services Objects
  • Solutions and Projects
  • File Formats
  • Fixed Width
  • Delimited
  • Ragged Right
  • Connection Managers
  • ADO Connection Manager
  • ADO.NET Connection Manager
  • Cache Connection Manager
  • Excel Connection Manager
  • File Connection Manager
  • Flat File Connection Manager
  • FTP Connection Manager
  • HTTP Connection Manager
  • MSMQ Connection Manager
  • Analysis Services Connection Manager
  • Multiple Files Connection Manager
  • Multiple Flat Files Connection Manager
  • ODBC Connection Manager
  • OLE DB Connection Manager
  • SMO Connection Manager
  • SMTP Connection Manager
  • SQL Server Compact Edition Connection Manager
  • WMI Connection Manager
  • Microsoft Connector 1.0 for SAP BI
  • Microsoft Connector for Oracle by Attunity
  • Microsoft Connector for Teradata by Attunity
  • Data Sources and Data Source Views
  • Data Sources
  • Data Source View
  • SSIS Variables
  • System Variables
  • Hands-On: Using System Variables to Create Custom Logs
  • User-Defined Variables
  • Hands-On: Creating a Directory with User-Defined Variables
  • Precedence Constraints
  • Constraint Options
  • Multiple Constraints
  • Integration Services Expressions
  • Hands-On: Using Expressions to Update Properties at Run Time
  • Summary
  • Chapter 4 Integration Services Control Flow Containers
  • Integration Services Package
  • Foreach Loop Container
  • Hands-On: Contacting Opportunities
  • For Loop Container
  • Hands-On: Deleting Data Month by Month After Archiving
  • Sequence Container
  • Task Host Container
  • Summary
  • Chapter 5 Integration Services Control Flow Tasks
  • Categories of Control Flow Tasks
  • Data Flow Task
  • Data Preparation Tasks
  • Workflow Tasks
  • SQL Server Tasks
  • Scripting Tasks
  • Analysis Services Tasks
  • Transfer Tasks
  • Maintenance Tasks
  • Backward Compatibility Tasks
  • Custom Tasks
  • Control Flow Tasks in Detail
  • FTP Task
  • Preparations for the Hands-On Exercises in This Chapter
  • Hands-On: Downloading Zipped Files
  • Execute Process Task
  • Hands-On: Expanding Downloaded Files
  • File System Task
  • Hands-On: Archiving Downloaded Files
  • Web Service Task
  • XML Task
  • Input Section
  • Second Operand Section
  • Output Section
  • Operation Options Section
  • Execute SQL Task
  • General Page
  • Parameter Mapping Page
  • Result Set Page
  • Expressions Page
  • Bulk Insert Task
  • Message Queue Task
  • Hands-On: Importing Expanded Files
  • Execute Package Task
  • Hands-On: Consolidating Workflow Packages
  • Send Mail Task
  • WMI Data Reader Task
  • Hands-On: Reading the Application Log
  • WMI Event Watcher Task
  • Transfer Database Task
  • Transfer Error Messages Task
  • Transfer Jobs Task
  • Transfer Logins Task
  • Transfer Master Stored Procedures Task
  • Transfer SQL Server Objects Task
  • Back Up Database Task
  • Check Database Integrity Task
  • Execute SQL Server Agent Job Task
  • Execute T-SQL Statement Task
  • History Cleanup Task
  • Maintenance Cleanup Task
  • Notify Operator Task
  • Rebuild Index Task
  • Reorganize Index Task
  • Shrink Database Task
  • Update Statistics Task
  • Summary
  • Chapter 6 Administering Integration Services
  • Connecting to Integration Services Service
  • Managing Packages with Default Settings
  • Managing Packages Saved on a Remote Server
  • Managing Packages on an Instance of an SQL Server
  • Connecting to Integration Services on a Remote Server
  • Managing SSIS Packages
  • Hands-On: Working with Integration Services Storage Folders
  • dtutil Utility
  • Hands-On: Using dtutil
  • Running SSIS Packages
  • SQL Server Import and Export Wizard
  • BIDS
  • Execute Package Utility (DTExecUI)
  • Hands-On: Running an SSIS Package Using the Execute Package Utility
  • DTExec Utility
  • SQL Server Agent
  • Hands-On: Automating Running an SSIS Package with SQL Server Agent
  • Executing SSIS Packages Programmatically
  • Summary
  • Chapter 7 Securing Integration Services Packages
  • Digitally Signing the Package
  • Excluding Sensitive Information from the Package
  • Encrypting Sensitive Information in the Package
  • Encrypting All the Information in the Package
  • Hands-On: Working with Package Protection Levels
  • Using Integration Services Fixed Database-Level Roles
  • Fixed Database-Level Roles and Their Permissions
  • Hands-On: Control Access to a Package with User-Defined Roles
  • Considerations for Different Storage Areas
  • Considerations for Saving to SQL Server
  • Considerations for Saving to the File System
  • Summary
  • Chapter 8 Advanced Features of Integration Services
  • Logging and Log Providers in SSIS
  • Hands-On: Configuring Logging in a Package
  • Transactions in Integration Services Packages
  • Hands-On: Maintaining Data Integrity with Transactions
  • Restarting Packages with Checkpoints
  • Hands-On: Restarting a Failed Package Using Checkpoints
  • Expressions and Variables
  • Hands-On: Extending the Contacting Opportunities Package with Property Expressions
  • Handling Events at Package Run Time
  • Hands-On: Creating Event Handlers in an SSIS Package
  • As a Data Source for Reporting Services Report
  • Enable SSIS as a Data Source
  • Using SSIS as a Data Source
  • Summary
  • Chapter 9 Data Flow Components
  • From Control Flow to Data Flow
  • Data Flow Component Interfaces
  • External Metadata
  • Inputs
  • Outputs
  • Error Outputs
  • Considerations for Bringing Data into Data Flow
  • Data Flow Sources
  • ADO NET Source
  • Excel Source
  • Flat File Source
  • OLE DB Source
  • Raw File Source
  • Script Component Source
  • XML Source
  • Data Flow Transformations
  • Business Intelligence Transformations
  • Row Transformations
  • Rowset Transformations
  • Split and Join Transformations
  • Auditing Transformations
  • Data Flow Destinations
  • ADO NET Destination
  • Data Mining Model Training Destination
  • DataReader Destination
  • Dimension Processing Destination
  • Excel Destination
  • Flat File Destination
  • OLE DB Destination
  • Partition Processing Destination
  • Raw File Destination
  • Recordset Destination
  • Script Component Destination
  • SQL Server Compact Destination
  • SQL Server Destination
  • Data Flow Paths
  • Hands-On: An Introduction to the Data Flow Task
  • Summary
  • Chapter 10 Data Flow Transformations
  • Row Transformations
  • Copy Column Transformation
  • Character Map Transformation
  • Data Conversion Transformation
  • Derived Column Transformation
  • Export Column Transformation
  • Import Column Transformation
  • Script Component
  • OLE DB Command Transformation
  • Split and Join Transformations
  • Conditional Split Transformation
  • Multicast Transformation
  • Union All Transformation
  • Merge Transformation
  • Merge Join Transformation
  • Cache Transform
  • Lookup Transformation
  • Hands-On: Updating PersonContact Data
  • Rowset Transformations
  • Sort Transformation
  • Percentage Sampling Transformation
  • Row Sampling Transformation
  • Pivot Transformation
  • Hands-On: Pivoting Sales Order Records in an Excel Worksheet
  • Unpivot Transformation
  • Aggregate Transformation
  • Hands-On: Aggregating SalesOrders
  • Audit Transformations
  • Audit Transformation
  • Row Count Transformation
  • Business Intelligence Transformations
  • Slowly Changing Dimension Transformation
  • Hands-On: Loading a Slowly Changing Dimension
  • Data Mining Query Transformation
  • Term Lookup Transformation
  • Term Extraction Transformation
  • Fuzzy Grouping Transformation
  • Fuzzy Lookup Transformation
  • Hands-On: Removing Duplicates from Owners Data
  • Summary
  • Chapter 11 Programming Integration Services
  • The Two Engines of Integration Services
  • Programming Options
  • Scripting
  • Developing Custom Objects from Scratch
  • Building Packages Programmatically
  • Extending Packages with Scripting
  • The Legacy Scripting Task: ActiveX Script Task
  • Script Task
  • Hands-On: Scripting the Handshake Functionality
  • Script Component
  • Script Task vs. Script Component
  • Hands-On: Extending Data Flow with the Script Component
  • Script Component as a Data Source
  • Script Component as a Transformation
  • Script Component as a Destination
  • Debugging Techniques for Script Component
  • Summary
  • Chapter 12 Data Warehousing and SQL Server 2008 Enhancements
  • The Need for a Data Warehouse
  • Data Warehouse Design Approaches
  • Top-Down Design Approach
  • Bottom-Up Design Approach
  • Data Warehouse Architecture
  • Centralized EDW with Dependent Data Marts
  • Distributed Independent Data Marts
  • Data Warehouse Data Models
  • Entity-Relationship Model
  • Dimensional Model
  • Dimension Types
  • Loading a Dimension Using a Slowly Changing Dimension
  • Data Model Schema
  • Star Schema
  • Snowflake Model
  • Building a Star Schema
  • SQL Server 2008 R2 Features and Enhancements
  • SQL Server 2008 R2 Data Warehouse Editions
  • SQL Server 2008 R2 Datacenter
  • SQL Server 2008 R2 Parallel Data Warehouse
  • SQL Server 2008 R2 Data Warehouse Solutions
  • Fast Track Data Warehouse
  • Parallel Data Warehouse
  • SQL Server 2008 R2 Data Warehouse Enhancements
  • Backup Compression
  • MERGE Statement
  • GROUP BY Extensions
  • Star Join Query Processing Enhancement
  • Change Data Capture
  • Partitioned Table Parallelism
  • Summary
  • Chapter 13 Deploying Integration Services Packages
  • Package Configurations
  • Types of Package Configurations
  • Hands-On: Applying Configurations to Contacting Opportunities
  • Direct and Indirect Configurations
  • Hands-On: Using Indirect Configurations
  • Deployment Utility
  • Deploying Integration Services Projects
  • Hands-On: Deploying an Integration Services Project
  • Custom Deployment
  • Summary
  • Chapter 14 Migrating to Integration Services 2008
  • Upgrade Advisor
  • Hands-On: Analyzing DTS 2000 Packages with SQL Server 2008 Upgrade Advisor
  • Migrating Data Transformation Services Packages
  • Migration Options
  • Installing DTS 2000 Support Components
  • Running DTS 2000 Packages As-Is with Run-Time Support
  • Hands-On: Executing a DTS 2000 Package
  • Embedding DTS 2000 Packages in Integration Services Packages
  • Execute DTS 2000 Package Task
  • Hands-On: Executing Importing Contacts Using the Execute DTS 2000 Package Task
  • Migrating DTS 2000 Packages to Integration Services
  • Package Migration Wizard
  • Hands-On: Migrating Importing Contacts to Integration Services
  • Upgrading Integration Services 2005
  • Same-Server Installation
  • Different Server Installation
  • Upgrading SSIS 2005 Packages
  • Summary
  • Chapter 15 Troubleshooting and Performance Enhancements
  • Troubleshooting Integration Services Packages
  • Debugging Features Available by Default
  • Debugging Tools Requiring Configuration
  • Hands-On: Setting Breakpoints to See Variables Values
  • Performance Enhancements
  • It’s All About Memory
  • Architecture of the Data Flow
  • Synchronous and Asynchronous Transformations
  • Classifying Data Flow Transformations
  • Optimization Techniques
  • Performance Monitoring Tools
  • Performance Counters
  • SQL Server Profiler
  • Logging
  • Execution Trees
  • Hands-On: Monitoring Log Events in a Pipeline
  • Using Parallel Processing
  • Running Parallel Tasks in the Control Flow
  • Creating Multiple Data Flows
  • Enhancing EngineThreads
  • Summary
  • Appendix: How to Use the Provided Software
  • Downloaded Software
  • Attaching Campaign Database
  • Index