Microsoft SQL Server 2012 Integration Services (Developer Reference)

Build and manage data integration solutions with expert guidance from the Microsoft SQL Server Integration Services (SSIS) team. See best practices in action and dive deep into the SSIS engine, SSISDB catalog, and security features. Using the developer enhancements in SQL Server 2012 and the flexible SSIS toolset, you'll handle complex data integration scenarios more efficiently - and acquire the skills you need to build comprehensive solutions. Foreword Introduction Part I: Overview Chapter 1: SSIS Overview Common Usage Scenarios for SSIS Consolidation of Data from Heterogeneous Data Sources Movement of Data Between Systems Loading a Data Warehouse Cleaning, Formatting, or Standardization of Data Identification, Capture, and Processing of Data Changes Coordination of Data Maintenance, Processing, or Analysis Evolution of SSIS Setting Up SSIS SQL Server Features Needed for Data Integration SQL Server Editions and Integration Services Features Summary Chapter 2: Understanding SSIS Concepts Control Flow Tasks Precedence Constraints Variables and Expressions Containers Connection Managers Packages and Projects Parameters Log Providers Event Handlers Data Flow Source Adapters Destination Adapters Transforms SSIS Catalog Overview Catalog Folders Environments References Summary Chapter 3: Upgrading to SSIS 2012 What’s New in SSIS 2012 Upgrade Considerations and Planning Feature Changes in SSIS Dependencies and Tools Upgrade Requirements Upgrade Scenarios Unsupported Upgrade Scenarios Upgrade Validation Integration Services Upgrade Upgrade Advisor Performing Upgrade Addressing Upgrade Issues and Manual Upgrade Steps Conversion to Projects after Upgrade Summary Part II: Development Chapter 4: New SSIS Designer Features The Integration Services Designer Visual Studio Undo and Redo Getting Started Window Toolbox Variables Window Zoom Control Autosave and Recovery Status Icons Annotations Configuration and Deployment Solution Explorer Changes Parameter Tab Visual Studio Configurations Project Compilation Deployment Wizard Project Conversion Wizard Import Project Wizard New Tasks and Data Flow Components Change Data Capture Expression Task DQS Cleansing Transform ODBC Source and Destination Control Flow Expression Adorners Connection Managers Execute SQL Task Data Flow Connection Assistants Improved Column Mapping Editing Components in an Error State Grouping Simplified Data Viewers Row Count and Pivot Transform User Interfaces Flat File Source Changes Scripting Visual Studio Tools for Applications Script Component Debugging .NET 4 Framework Support Expressions Removal of the Character Limit New Expression Functions Summary Chapter 5: Team Development Improvements in SQL Server 2012 Package Format Changes Visual Studio Configurations Using Source Control Management with SSIS Connecting to Team Foundation Server Adding an SSIS Project to Team Foundation Server Change Management Changes to the SSIS Visual Studio Project File Best Practices Using Small, Simple Packages One Developer Per Package Consistent Naming Conventions Summary Chapter 6: Developing an SSIS Solution SSIS Project Deployment Models Package Deployment Model Project Deployment Model Develop an Integration Services Project Creating an SSIS Project Designing an Integration Services Data Flow Using Parameters and the ForEach Container Using the Execute Package Task Building and Deploying an Integration Services Project Summary Chapter 7: Understanding SSIS Connectivity Previous Connectivity Options in SSIS Providers for Connectivity Technology OLE DB, ADO.NET, and ODBC New Connectivity Options in SSIS 2012 Introducing ODBC ODBC Components for SSIS ODBC Source ODBC Destination Connectivity Considerations for SSIS 64-Bit and SSIS SSIS Tools on 64-Bit Architecture Connectivity to Other Sources and Destinations Connecting to Microsoft Excel and Access Connecting to Oracle Creating Custom Components Using Script Components Summary Chapter 8: Working with Change Data Capture in SSIS 2012 CDC in SQL Server Using CDC in SQL Server CDC Scenarios in ETLs Stages in CDC CDC in SSIS 2012 CDC State CDC Control Task Data Flow Component: CDC Source CDC Splitter Component CDC for Oracle Introduction Components for Creating CDC for Oracle CDC Service Configuration MMC Oracle CDC Designer MMC MSXDBCDC Database Oracle CDC Service Executable (xdbcdcsvc.exe) Data Type Handling SSIS CDC Components Summary Chapter 9: Data Cleansing Using SSIS Data Profiling Task Fuzzy Lookup Transformation Fuzzy Grouping Transformation Data Quality Services Cleansing Transform Summary Part III: Database Admin Chapter 10: Configuration in SSIS Configuration Basics How Configurations Are Applied What to Configure Changes in SSIS 2012 Configuration in SSIS 2012 Parameters Creating Package Parameters Creating Project Parameters API for Creating Parameters Using Parameters Configuring Parameters on the SSIS Catalog Configuring, Validating, and Executing Packages and Projects Configuration Through SSMS Configuration Using SQL Agent, DTExec, and T-SQL SSIS Environments Evaluation Order of Parameters Package Deployment Model and Backward Compatibility Package Deployment Model Best Practices for Configuring SSIS Best Practices with Package Deployment Model Best Practices with Project Deployment Model Summary Chapter 11: Running SSIS Packages Ways to Run SSIS Packages Package Locations Configuring Packages Error Dumps Logging Options Running Packages in the SSIS Catalog Prepare Executions Starting SSIS Package Executions View Executions Executions with T-SQL Running Packages from SQL Agent Create an SSIS Job Step Execute Packages from the SSIS Catalog Running Packages via PowerShell Creating and Running SSIS Packages Programmatically Summary Chapter 12: SSIS T-SQL Magic Overview of SSIS Stored Procedures and Views Integration Services Catalog SSIS Catalog Properties Querying the SSIS Catalog Properties Setting SSIS Catalog Properties SSIS Projects and Packages Deploy an SSIS Project to the SSIS Catalog Learning About the SSIS Projects Deployed to the SSIS Catalog Configuring SSIS Projects Managing SSIS Projects in the SSIS Catalog Running SSIS Packages in the SSIS Catalog SSIS Environments Creating SSIS Environments Creating SSIS Environment Variables Configuring SSIS Projects Using SSIS Environments Configuring SSIS Projects Using Reference Values Package Execution Using SSIS Environments Managing SSIS Environment and Environment Variables Summary Chapter 13: SSIS PowerShell Magic PowerShell Refresher PowerShell and SQL Server Managing SSIS with PowerShell SSIS Management Object Model PowerShell with SSIS Management Object Model PowerShell and SSIS Using T-SQL Advantages of Using PowerShell with SSIS Summary Chapter 14: SSIS Reports Getting Started with SSIS Reports Data Preparation Monitoring SSIS Package Execution Integration Services Dashboard All Executions Report All Validations and All Operations Reports Using SSIS Reports to Troubleshoot SSIS Package Execution Using the Execution Performance Report to Identify Performance Trends Summary Part IV: Deep-Dive Chapter 15: SSIS Engine Deep Dive The Control Flow Engine Overview Load Apply Parameters Validate Execute The Data Flow Engine Overview Execution Control Backpressure Engine Tuning Summary Chapter 16: SSIS Catalog Deep Dive SSIS Catalog Deep Dive Creating the SSIS Catalog Unit of Deployment to the SSIS Catalog What Is Inside SSISDB? SQL Server Instance Starts Up SSIS Catalog and Logging Levels Understanding the SSIS Package Execution Life Cycle Stopping SSIS Package Executions Using the Windows Application Event Log SSIS Catalog Maintenance and SQL Server Agent Jobs Backup and Restore of the SSIS Catalog Back Up SSISDB Restore SSISDB Summary Chapter 17: SSIS Security Protect Your Package Control Package Access Package Encryption Sensitive Variables and Parameters Package Signing Security in the SSIS Catalog Security Overview Manage Permissions DDL Trigger Running SSIS with SQL Agent Requirements Create Credentials Create Proxy Accounts Create SQL Agent Jobs Summary Chapter 18: Understanding SSIS Logging Configure Logging Options Choose Containers Select Events Add Log Providers Log Providers Text Files SQL Server SQL Server Profiler Windows Event Log XML Files Logging in the SSIS Catalog Logging Levels Event Logs Event Context Information Advanced Logging Topics Customizing Logging Fields Logging with dtexec Utility Developing Custom Log Providers Summary Chapter 19: Automating SSIS Introduction to SSIS Automation Programmatic Generation of SSIS Packages Metadata-Driven Package Execution Dynamic Package Generation Handling Design-Time Events Samples Metadata-Based Execution Custom Package Runner Using PowerShell with the SSIS Management Object Model Using PowerShell with SQL Agent Alternative Solutions and Samples Samples on Codeplex Third-Party Solutions Summary Part V: Troubleshooting Chapter 20: Troubleshooting SSIS Package Failures Getting Started with Troubleshooting Data Preparation Troubleshooting Failures of SSIS Package Executions Three Key Steps Toward Troubleshooting Failures of SSIS Package Executions Execution Path Finding the Root Cause of Failure Troubleshooting the Execute Package Task and Child Package Executions DiagnosticEx Events Execute Package Task and Execution Path Troubleshooting SSIS Package Execution Failures Scheduled with SQL Agent Using CallerInfo to Determine SSIS Package Executions That Are Executed by SQL Agent Using SQL Agent History Tables to Determine the SSIS Job Steps That Failed Summary Chapter 21: SSIS Performance Best Practices Creating a Performance Strategy OVAL Technique Measuring SSIS Performance Measuring System Performance Measuring Performance of Data Flow Tasks Designing for Performance Parallelize Your Design Using SQL Server Optimization Techniques Bulk Loading Your Data Keeping SSIS Operations in Memory Optimizing SSIS Lookup Caching Optimizing SSIS Infrastructure Summary Chapter 22: Troubleshooting SSIS Performance Issues Performance Profiling Troubleshooting Performance Issues Data Preparation Understanding SSIS Package Execution Performance SSIS Package Execution Duration Time Spent at Each Task in the SSIS Package Time Spent at Each Phase of the Data Flow Component Elapsed Time for Data Flow Component Phases (Active Time vs. Total Time) Monitoring SSIS Package Execution Performance Per-Execution Performance Counters Interactive Analysis of Performance Data Summary Chapter 23: Troubleshooting Data Issues Troubleshooting in the Design Environment Row Count Values Data Viewers Data in Error Output Breakpoints and Debug Windows Troubleshooting in the Execution Environment Execution Data Statistics Data Tap Error Dumps Summary Index About the Authors Build and manage data integration solutions with expert guidance from the Microsoft SQL Server Integration Services (SSIS) team. See best practices in action and dive deep into the SSIS engine, SSISDB catalog, and security features. Using the developer enhancements in SQL Server 2012 and the flexible SSIS toolset, you'll handle complex data integration scenarios more efficiently—and acquire the skills you need to build comprehensive solutions. Discover how to: Use SSIS to extract, transform, and load data from multiple data sources Apply best practices to optimize package and project configuration and deployment Manage security settings in the SSISDB catalog and control package access Work with SSIS data quality features to profile, cleanse, and increase reliability Monitor, troubleshoot, and tune SSIS solutions with advanced features such as detailed views and data taps Load data incrementally to capture an easily consumable stream of insert, update, and delete activity "Integrate data from across the enterprise--and improve business analysis. Build and manage data integration solutions with expert guidance from the Microsoft SQL Server Integration Services (SSIS) team. See best practices in action and dive deep into the SSIS engine, SSISDB catalog, and security features. Using the developer enhancements in SQL Server 2012 and the flexible SSIS toolset, you'll handle complex data integration scenarios more efficiently--and acquire the skills you need to build comprehensive solutions. Discover how to: Use SSIS to extract, transform, and load data from multiple data sources; Apply best practices to optimize package and project configuration and deployment; Manage security settings in the SSISDB catalog and control package access; Work with SIS data quality features to profile, cleanse, and increase reliability..."--P. [4] of cover