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