Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Table of Contents

• Preface

• Chapter 1: Getting Started with SQL Server Integration Services
• Introduction
• Import and Export Wizard: First experience with SSIS
• Getting started with SSDT
• Creating the first SSIS Package
• Getting familiar with Data Flow Task
• SSIS 2012 versus previous versions in Developer Experience

• Chapter 2: Control Flow Tasks
• Introduction
• Executing T-SQL commands: Execute SQL Task
• Handling file and folder operations: File System Task
• Sending and receiving files through FTP: FTP Task
• Executing other packages: Execute Package Task
• Running external applications: Execute Process Task
• Reading data from web methods: Web Service Task
• Transforming, validating, and querying XML: XML Task
• Profiling table statistics: Data Profiling Task
• Batch insertion of data into a database: Bulk Insert Task
• Querying system information: WMI Data Reader Task
• Querying system events: WMI Event Watcher Task
• Transferring SQL server objects: DBMS Tasks

• Chapter 3: Data Flow Task Part 1—Extract and Load
• Introduction
• Working with database connections in Data Flow
• Working with flat files in Data Flow
• Passing data between packages—Raw Source and Destination
• Importing XML data with XML Source
• Loading data into memory—Recordset Destination
• Extracting and loading Excel data
• Change Data Capture

• Chapter 4: Data Flow Task Part 2—Transformations
• Introduction
• Derived Column: adding calculated columns
• Audit Transformation: logging in Data Flow
• Aggregate Transform: aggregating the data stream
• Conditional Split: dividing the data stream based on conditions
• Lookup Transform: performing the Upsert scenario
• OLE DB Command: executing SQL statements on each row in the data stream
• Merge and Union All transformations: combining input data rows
• Merge Join Transform: performing different types of joins in data flow
• Multicast: creating copies of the data stream
• Working with BLOB fields: Export Column and Import Column transformations
• Slowly Changing Dimensions (SCDs) in SSIS

• Chapter 5: Data Flow Task Part 3—Advanced Transformation
• Introduction
• Pivot and Unpivot Transformations
• Text Analysis with Term Lookup and Term Extraction transformations
• DQS Cleansing Transformation—Cleansing Data
• Fuzzy Transformations—how SSIS understands fuzzy similarities

• Chapter 6: Variables, Expressions, and Dynamism in SSIS
• Introduction
• Variables and data types
• Using expressions in Control Flow
• Using expressions in Data Flow
• The Expression Task
• Dynamic connection managers
• Dynamic data transfer with different data structures

• Chapter 7: Containers and Precedence Constraints
• Introduction
• Sequence Container: putting all tasks in an executable object
• For Loop Container: looping through static enumerator till a condition is met
• Foreach Loop Container: looping through result set of a database query
• Foreach Loop Container: looping through files using File Enumerator
• Foreach Loop Container: looping through data table
• Precedence Constraints: how to control the flow of task execution

• Chapter 8: Scripting
• Introduction
• The Script Task: Scripting through Control Flow
• The Script Component as a Transformation
• The Script Component as a Source
• The Script Component as a Destination
• The Asynchronous Script Component

• Chapter 9: Deployment
• Introduction
• Project Deployment Model: Project Deployment from SSDT
• Using Integration Services Deployment Wizard and command-line utility for deployment
• The Package Deployment Model, Using SSDT to deploy package
• Creating and running Deployment Utility
• DTUTIL—the command-line utility for deployment
• Protection level: Securing sensitive data

• Chapter 10: Debugging, Troubleshooting, and Migrating Packages to 2012
• Introduction
• Troubleshooting with Progress and Execution Results tab
• Breakpoints, Debugging the Control Flow
• Handling errors in Data Flow
• Migrating packages to 2012
• Data Tap

• Chapter 11: Event Handling and Logging
• Introduction
• Logging over Legacy Deployment Model
• Logging over Project Deployment Model
• Using event handlers and system variables for custom logging

• Chapter 12: Execution
• Introduction
• Execution from SSMS
• Execution from a command-line utility
• Execution from a scheduled SQL Server Agent job

• Chapter 13: Restartability and Robustness
• Introduction
• Parameters: Passing values to packages from outside
• Package configuration: Legacy method to inter-relation
• Transactions: Doing multiple operations atomic
• Checkpoints: The power of restartability
• SSIS reports and catalog views

• Chapter 14: Programming SSIS
• Introduction
• Creating and configuring Control Flow Tasks programmatically
• Working with Data Flow components programmatically
• Executing and managing packages programmatically
• Creating and using Custom Tasks

• Chapter 15: Performance Boost in SSIS
• Introduction
• Control Flow Task and variables considerations for boosting performance
• Data Flow best practices in Extract and Load
• Data Flow best practices in Transformations
• Working with buffer size
• Working with performance counters

• Index