Hjem

INTRODUCTION xxix
CHAPTER 1: WELCOME TO SQL SERVER INTEGRATION SERVICES1 SQL Server SSIS Historical Overview 2 What''s New in SSIS 2 Tools of the Trade 2 Import and Export Wizard 3 The SQL Server Data Tools Experience 4 SSIS Architecture 5 Packages 5 Control Flow 6 Data Flow 9 Variables 13 Parameters 14 Error Handling and Logging 14 Editions of SQL Server 14 Summary 15
CHAPTER 2: THE SSIS TOOLS 17 Import and Export Wizard 17 SQL Server Data Tools 23 Creating Your First Package 25 The Solution Explorer Window 26 The Toolbox 27 The Properties Windows 28 The SSIS Package Designer 28 Control Flow 29 Connection Managers 32 Variables 32 Data Flow 34 Parameters 34 Event Handlers 34 Package Explorer 35 Executing a Package 36 Management Studio 36 Summary 37
CHAPTER 3: SSIS TASKS 39 SSIS Task Objects 40 Using the Task Editor 40 The Task Editor Expressions Tab 40 SSDTCommon Properties 41 Looping and Sequence Tasks 42 Script Task (.NET) 43 Analysis Services Tasks 45 Analysis Services Execute DDL Task 45 Analysis Services Processing Task 46 Data Mining Query Task 46 Data Flow Task 47 Data Preparation Tasks 48 Data Profi ler 48 File System Task 50 Archiving a File 52 FTP Task 53 Getting a File Using FTP 54 Web Service Task 55 Retrieving Data Using the Web Service Task and XML SourceComponent 57 XML Task 60 Validating an XML File 62 RDBMS Server Tasks 64 Bulk Insert Task 64 Using the Bulk Insert Task 67 Execute SQL Task 68 Workfl ow Tasks 80 Execute Package Task 80 Execute Process Task 81 Message Queue Task 82 Send Mail Task 83 WMI Data Reader Task 84 WMI Event Watcher Task 86 Polling a Directory for the Delivery of a File 86 SMO Administration Tasks 87 Transfer Database Task 88 Transfer Error Messages Task 89 Transfer Logins Task 89 Transfer Master Stored Procedures Task 90 Transfer Jobs Task 91 Transfer SQL Server Objects Task 91 Summary 92
CHAPTER 4: CONTAINERS 93 Task Host Containers 93 Sequence Containers 94 Groups 95 For Loop Container 95 Foreach Loop Container 97 Foreach File Enumerator Example 98 Foreach ADO Enumerator Example 100 Summary 103
CHAPTER 5: THE DATA FLOW 105 Understanding the Data Flow 105 Data Viewers 106 Sources 106 OLE DB Source 107 Excel Source 109 Flat File Source 110 Raw File Source 114 XML Source 115 ADO.NET Source 115 Destinations 115 Excel Destination 116 Flat File Destination 116 OLE DB Destination 116 Raw File Destination 117 Recordset Destination 117 SQL Server and Mobile Destinations 118 Data Mining Model Training 118 DataReader Destination 118 Dimension and Partition Processing 118 Common Transformations 119 Synchronous versus Asynchronous Transformations 119 Aggregate 119 Conditional Split 121 Data Conversion 122 Derived Column 122 Lookup 123 Row Count 124 Script Component 125 Slowly Changing Dimension 126 Sort 126 Union All 127 Other Transformations 128 Audit 128 Character Map 129 Copy Column 130 Data Mining Query 130 DQS Cleansing 131 Export Column 131 Fuzzy Lookup 133 Fuzzy Grouping 138 Import Column 142 Merge 144 Merge Join 144 Multicast 145 OLE DB Command 145 Percentage and Row Sampling 147 Pivot Transform 147 Unpivot 150 Term Extraction 152 Term Lookup 156 Data Flow Example 157 Summary 160
CHAPTER 6: USING VARIABLES, PARAMETERS, AND EXPRESSIONS161 Dynamic Package Objects 162 Variable Overview 162 Parameter Overview 162 Expression Overview 163 Understanding Data Types 164 SSIS Data Types 164 Date and Time Type Support 166 How Wrong Data Types and Sizes Can Affect Performance 167 Unicode and Non-Unicode Conversion Issues 167 Casting in SSIS Expressions 169 Using Variables and Parameters 170 Defi ning Variables 170 Defining Parameters 171 Variable and Parameter Data Types 172 Working with Expressions 173 C#-Like? Close, but Not Completely 174 The Expression Builder 175 Syntax Basics 176 Using Expressions in SSIS Packages 190 Summary 200
CHAPTER 7: JOINING DATA 201 The Lookup Transformation 202 Using the Merge Join Transformation 203 Contrasting SSIS and the Relational Join 203 Lookup Features 206 Building the Basic Package 207 Using a Relational Join in the Source 209 Using the Merge Join Transformation 211 Using the Lookup Transformation 216 Full-Cache Mode 216 No-Cache Mode 219 Partial-Cache Mode 220 Multiple Outputs 223 Expressionable Properties 226 Cascaded Lookup Operations 227 Cache Connection Manager and Cache Transform 229 Summary 231
CHAPTER 8: CREATING AN END-TO-END PACKAGE 233 Basic Transformation Tutorial 233 Creating Connections 234 Creating the Control Flow 237 Creating the Data Flow 237 Completing the Package 239 Saving the Package 239 Executing the Package 240 Typical Mainframe ETL with Data Scrubbing 241 Creating the Data Flow 242 Handling Dirty Data 242 Finalizing 246 Handling More Bad Data 247 Looping and the Dynamic Tasks 250 Looping 250 Making the Package Dynamic 250 Summary 252
CHAPTER 9: SCRIPTING IN SSIS 253 Introducing SSIS Scripting 253 Getting Started in SSIS Scripting 255 Selecting the Scripting Language 255 Using the VSTA Scripting IDE 256 Example: Hello World 257 Adding Code and Classes 259 Using Managed Assemblies 260 Example: Using Custom
.NET Assemblies 261 Using the Script Task 264 Confi guring the Script Task Editor 265 The Script Task Dts Object 266 Accessing Variables in the Script Task 267 Connecting to Data Sources in a Script Task 271 Raising an Event in a Script Task 281 Writing a Log Entry in a Script Task 287 Using the Script Component 288 Diff erences from a Script Task 288 Confi guring the Script Component Editor 289 Accessing Variables in a Script Component 291 Connecting to Data Sources in a Script Component 292 Raising Events 292 Logging 293 Example: Data Validation 294 Synchronous versus Asynchronous 302 Essential Coding, Debugging, and Troubleshooting Techniques305 Structured Exception Handling 305 Script Debugging and Troubleshooting 308 Summary 311
CHAPTER 10: LOADING A DATA WAREHOUSE 313 Data Profiling 315 Initial Execution of the Data Profiling Task 315 Reviewing the Results of the Data Profiling Task 317 Turning Data Profi le Results into Actionable ETL Steps 321 Data Extraction and Cleansing 322 Dimension Table Loading 322 Loading a Simple Dimension Table 323 Loading a Complex Dimension Table 327 Considerations and Alternatives to the SCD Transformation335 Fact Table Loading 337 SSAS Processing 345 Using a Master ETL Package 350 Summary 351
CHAPTER 11: ADVANCED DATA CLEANSING IN SSIS 353 Advanced Derived Column Use 354 Text Parsing Example 355 Advanced Fuzzy Lookup and Fuzzy Grouping 357 Fuzzy Lookup 357 Fuzzy Grouping 363 DQS Cleansing 366 Data Quality Services 366 DQS Cleansing Transformation 370 Summary 373
CHAPTER 12: USING THE RELATIONAL ENGINE 375 Data Extraction 376 SELECT * Is Bad 376 WHERE Is Your Friend 377 Transform during Extract 378 Many ANDs Make Light Work 381 SORT in the Database 382 Modularize 384 SQL Server Does Text Files Too 385 Using Set-Based Logic 389 SQL Server Change Data Capture 391 Benefi ts of SQL Server CDC 392 Preparing CDC 393 Capture Instance Tables 394 The CDC API 396 Using the New SSIS CDC Tools 398 Querying CDC in SSIS 401 Data Loading 405 Database Snapshots 406 The MERGE Operator 408 Summary 411
CHAPTER 13: ACCESSING HETEROGENEOUS DATA 413 Excel and Access 415 64-Bit Support 415 Working with Excel Files 417 Working with Access 421 Importing from Oracle 427 Oracle Client Setup 428 Importing Oracle Data 429 Using XML and Web Services 431 Confi guring the Web Service Task 431 Working with XML Data as a Source 439 Flat Files 442 Loading Flat Files 443 Extracting Data from Flat Files 444 ODBC 447 Other Heterogeneous Sources 450 Summary 451
CHAPTER 14: RELIABILITY AND SCALABILITY 453 Restarting Packages 453 Simple Control Flow 455 Containers within Containers and Checkpoints 457 Variations on a Theme 459 Inside the Checkpoint File 461 Package Transactions 463 Single Package, Single Transaction 464