Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition
1st Edition
Published on 2. September 2010
758 pages
978-0-07-173641-1 (ISBN)
Description
Transform Raw Data Into Meaningful Business Intelligence with SSIS Deploy and manage high-performance data transformation solutions across your enterprise using the step-by-step techniques in this fully revised guide. Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition explains the tools and methods necessary to extract conclusive business intelligence from disparate corporate data. Learn how to build and secure packages, load and cleanse data, establish workflow, and optimize performance. Real-world examples, detailed illustrations, and hands-on exercises are included throughout this practical resource. Build SSIS packages using the Import and Export Wizard and Business Intelligence Development Studio Create Loop and Task Host Containers with Control Flow Designer Administer SSIS from SQL Server Management Studio and dutil Secure packages using encryption, roles, signatures, and permissions Build-in memory lookups and transformations using the Data Flow Engine Extend functionality using custom scripts and ActiveX components Use SCD transformations to load data warehouses and data marts Learn to build a Star Schema and Parallel Data Warehouse Migrate Data Transformation Services 2000 and SSIS 2005 packages
More details
Other editions
Additional editions
Book
11/2010
2nd Edition
Osborne/McGraw-Hill
€61.50
Article not available at the moment
Content
- Cover Page
- Hands-On Microsoft® SQL Server® 2008 Integration Services
- Copyright Page
- 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
