Microsoft SQL server 2012 integration services: an expert cookbook

Cover......Page 1
Copyright......Page 3
Credits......Page 4
Foreword......Page 6
About the Authors......Page 8
About the Reviewers......Page 10
www.PacktPub.com......Page 12
Table of Contents......Page 14
Preface......Page 18
 Introduction......Page 24
 Import and Export Wizard: First experience with SSIS......Page 26
 Getting started with SSDT......Page 34
 Creating the first SSIS Package......Page 42
 Getting familiar with Data Flow Task......Page 44
 SSIS 2012 versus previous versions in Developer Experience......Page 47
 Introduction......Page 52
 Executing T-SQL commands: Execute SQL Task......Page 53
 Handling file and folder operations: File System Task......Page 60
 Sending and receiving files through FTP: FTP Task......Page 65
 Executing other packages: Execute Package Task......Page 69
 Running external applications: Execute Process Task......Page 74
 Reading data from web methods: Web Service Task......Page 77
 Transforming, validating, and querying XML: XML Task......Page 81
 Profiling table statistics: Data Profiling Task......Page 90
 Batch insertion of data into a database: Bulk Insert Task......Page 93
 Querying system information: WMI Data Reader Task......Page 97
 Querying system events: WMI Event Watcher Task......Page 101
 Transferring SQL server objects: DBMS Tasks......Page 104
 Introduction......Page 108
 Working with database connections in Data Flow......Page 109
 Working with flat files in Data Flow......Page 121
 Passing data between packages—Raw Source and Destination......Page 125
 Importing XML data with XML Source......Page 130
 Loading data into memory—Recordset Destination......Page 135
 Extracting and loading Excel data......Page 138
 Change Data Capture......Page 144
 Introduction......Page 152
 Derived Column: adding calculated columns......Page 153
 Audit Transformation: logging in Data Flow......Page 156
 Aggregate Transform: aggregating the data stream......Page 160
 Conditional Split: dividing the data stream based on conditions......Page 165
 Lookup Transform: performing the Upsert scenario......Page 169
 OLE DB Command: executing SQL statements on each row in the data stream......Page 178
 Merge and Union All transformations: combining input data rows......Page 182
 Merge Join Transform: performing different types of joins in data flow......Page 185
 Multicast: creating copies of the data stream......Page 194
 Working with BLOB fields: Export Column and Import Column transformations......Page 197
 Slowly Changing Dimensions (SCDs) in SSIS......Page 202
 Introduction......Page 210
 Pivot and Unpivot Transformations......Page 211
 Text Analysis with Term Lookup and Term Extraction transformations......Page 224
 DQS Cleansing Transformation—Cleansing Data......Page 231
 Fuzzy Transformations—how SSIS understands fuzzy similarities......Page 237
 Introduction......Page 246
 Variables and data types......Page 247
 Using expressions in Control Flow......Page 254
 Using expressions in Data Flow......Page 257
 The Expression Task......Page 263
 Dynamic connection managers......Page 266
 Dynamic data transfer with different data structures......Page 273
 Introduction......Page 278
 Sequence Container: putting all tasks in an executable object......Page 279
 For Loop Container: looping through static enumerator till a condition is met......Page 283
 Foreach Loop Container: looping through result set of a database query......Page 288
 Foreach Loop Container: looping through files using File Enumerator......Page 294
 Foreach Loop Container: looping through data table......Page 299
 Precedence Constraints: how to control the flow of task execution......Page 306
 Introduction......Page 312
 The Script Task: Scripting through Control Flow......Page 313
 The Script Component as a Transformation......Page 316
 The Script Component as a Source......Page 320
 The Script Component as a Destination......Page 327
 The Asynchronous Script Component......Page 332
 Introduction......Page 340
 Project Deployment Model: Project Deployment from SSDT......Page 341
 Using Integration Services Deployment Wizard and command-line utility for deployment......Page 348
 The Package Deployment Model, Using SSDT to deploy package......Page 352
 Creating and running Deployment Utility......Page 358
 DTUTIL—the command-line utility for deployment......Page 361
 Protection level: Securing sensitive data......Page 365
 Introduction......Page 372
 Troubleshooting with Progress and Execution Results tab......Page 373
 Breakpoints, Debugging the Control Flow......Page 377
 Handling errors in Data Flow......Page 384
 Migrating packages to 2012......Page 390
 Data Tap......Page 394
 Introduction......Page 400
 Logging over Legacy Deployment Model......Page 401
 Logging over Project Deployment Model......Page 406
 Using event handlers and system variables for custom logging......Page 415
 Introduction......Page 426
 Execution from SSMS......Page 427
 Execution from a command-line utility......Page 432
 Execution from a scheduled SQL Server Agent job......Page 437
 Introduction......Page 446
 Parameters: Passing values to packages from outside......Page 447
 Package configuration: Legacy method to inter-relation......Page 459
 Transactions: Doing multiple operations atomic......Page 470
 Checkpoints: The power of restartability......Page 476
 SSIS reports and catalog views......Page 481
 Introduction......Page 488
 Creating and configuring Control Flow Tasks programmatically......Page 489
 Working with Data Flow components programmatically......Page 495
 Executing and managing packages programmatically......Page 504
 Creating and using Custom Tasks......Page 508
 Introduction......Page 520
 Control Flow Task and variables considerations for boosting performance......Page 521
 Data Flow best practices in Extract and Load......Page 525
 Data Flow best practices in Transformations......Page 529
 Working with buffer size......Page 537
 Working with performance counters......Page 539
Index......Page 544