Schweitzer Fachinformationen
Wenn es um professionelles Wissen geht, ist Schweitzer Fachinformationen wegweisend. Kunden aus Recht und Beratung sowie Unternehmen, öffentliche Verwaltungen und Bibliotheken erhalten komplette Lösungen zum Beschaffen, Verwalten und Nutzen von digitalen und gedruckten Medien.
Introduction xxvii
Chapter 1: Welcome to SQL Server Integration Services 1
SQL Server SSIS Historical Overview 2
What's New in SSIS 2
Tools of the Trade 3
Import and Export Wizard 3
The SQL Server Data Tools Experience 4
SSIS Architecture 5
Packages 5
Control Flow 5
Data Flow 9
Variables 14
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 24
The Solution Explorer Window 26
The SSIS Toolbox 27
The Properties Windows 28
The SSIS Package Designer 28
Control Flow 29
Connection Managers 32
Variables 33
Data Flow 34
Parameters 35
Event Handlers 35
Package Explorer 36
Executing a Package 37
Management Studio 37
Summary 37
Chapter 3: SSIS Tasks 39
SSIS Task Objects 40
Using the Task Editor 40
The Task Editor Expressions Tab 41
Looping and Sequence Tasks 41
Script Task (.NET) 41
Analysis Services Tasks 44
Analysis Services Execute DDL Task 44
Analysis Services Processing Task 44
Data Mining Query Task 46
Data Flow Task 47
Data Preparation Tasks 48
Data Profiler 48
File System Task 50
Archiving a File 52
FTP Task 53
Getting a File Using FTP 54
Web Service Task 56
Retrieving Data Using the Web Service Task and XML Source Component 59
XML Task 62
Validating an XML File 64
RDBMS Server Tasks 66
Bulk Insert Task 66
Using the Bulk Insert Task 69
Execute SQL Task 71
Workflow Tasks 82
Execute Package Task 82
Execute Process Task 84
Message Queue Task 86
Send Mail Task 87
WMI Data Reader Task 88
WMI Event Watcher Task 91
Polling a Directory for the Delivery of a File 91
SMO Administration Tasks 92
Transfer Database Task 93
Transfer Error Messages Task 94
Transfer Logins Task 94
Transfer Master Stored Procedures Task 95
Transfer Jobs Task 96
Transfer SQL Server Objects Task 96
Summary 97
Chapter 4: The Data Flow 99
Understanding the Data Flow 99
Data Viewers 100
Sources 101
OLE DB Source 102
Excel Source 104
Flat File Source 105
Raw File Source 110
XML Source 110
ADO.NET Source 111
Destinations 111
Excel Destination 112
Flat File Destination 112
OLE DB Destination 112
Raw File Destination 113
Recordset Destination 114
Data Mining Model Training 114
DataReader Destination 114
Dimension and Partition Processing 114
Common Transformations 115
Synchronous versus Asynchronous Transformations 115
Aggregate 115
Conditional Split 117
Data Conversion 118
Derived Column 119
Lookup 121
Row Count 121
Script Component 122
Slowly Changing Dimension 123
Sort 123
Union All 125
Other Transformations 126
Audit 126
Character Map 128
Copy Column 128
Data Mining Query 129
DQS Cleansing 130
Export Column 130
Fuzzy Lookup 132
Fuzzy Grouping 139
Import Column 143
Merge 145
Merge Join 146
Multicast 146
OLE DB Command 147
Percentage and Row Sampling 148
Pivot Transform 149
Unpivot 152
Term Extraction 154
Term Lookup 158
Data Flow Example 160
Summary 164
Chapter 5: Using Variables, Parameters, and Expressions 165
Dynamic Package Objects 166
Variable Overview 166
Parameter Overview 166
Expression Overview 167
Understanding Data Types 168
SSIS Data Types 168
Date and Time Type Support 170
How Wrong Data Types and Sizes Can Affect Performance 171
Unicode and Non-Unicode Conversion Issues 171
Casting in SSIS Expressions 173
Using Variables and Parameters 174
Defining Variables 174
Defining Parameters 175
Variable and Parameter Data Types 176
Working with Expressions 177
C#-Like? Close, but Not Completely 178
The Expression Builder 179
Syntax Basics 180
Using Expressions in SSIS Packages 194
Summary 204
Chapter 6: Containers 205
Task Host Containers 205
Sequence Containers 206
Groups 207
For Loop Container 207
Foreach Loop Container 210
Foreach File Enumerator Example 211
Foreach ADO Enumerator Example 213
Summary 218
Chapter 7: Joining Data 219
The Lookup Transformation 220
Using the Merge Join Transformation 221
Contrasting SSIS and the Relational Join 222
Lookup Features 224
Building the Basic Package 225
Using a Relational Join in the Source 227
Using the Merge Join Transformation 230
Using the Lookup Transformation 235
Full-Cache Mode 235
No-Cache Mode 239
Partial-Cache Mode 240
Multiple Outputs 243
Expressionable Properties 246
Cascaded Lookup Operations 247
Cache Connection Manager and Cache Transform 249
Summary 252
Chapter 8: Creating an End-to-End Package 253
Basic Transformation Tutorial 253
Creating Connections 254
Creating the Control Flow 257
Creating the Data Flow 257
Completing the Package 259
Saving the Package 260
Executing the Package 260
Typical Mainframe ETL with Data Scrubbing 261
Creating the Data Flow 263
Handling Dirty Data 263
Finalizing 268
Handling More Bad Data 269
Looping and the Dynamic Tasks 271
Looping 271
Making the Package Dynamic 272
Summary 274
Chapter 9: Scripting in SSIS 275
Introducing SSIS Scripting 276
Getting Started in SSIS Scripting 277
Selecting the Scripting Language 277
Using the VSTA Scripting IDE 278
Example: Hello World 279
Adding Code and Classes 281
Using Managed Assemblies 282
Example: Using Custom .NET Assemblies 283
Using the Script Task 286
Configuring the Script Task Editor 287
The Script Task Dts Object 288
Accessing Variables in the Script Task 289
Connecting to Data Sources in a Script Task 293
Raising an Event in a Script Task 303
Writing a Log Entry in a Script Task 309
Using the Script Component 310
Differences from a Script Task 310
Configuring the Script Component Editor 311
Accessing Variables in a Script Component 313
Connecting to Data Sources in a Script Component 314
Raising Events 314
Logging 315
Example: Data Validation 316
Synchronous versus Asynchronous 324
Essential Coding, Debugging, and Troubleshooting Techniques 327
Structured Exception Handling 327
Script Debugging and Troubleshooting 330
Summary 333
Chapter 10: Advanced Data Cleansing in SSIS 335
Advanced Derived Column Use 336
Text Parsing Example 338
Advanced Fuzzy Lookup and Fuzzy Grouping 340
Fuzzy Lookup 340
Fuzzy Grouping 347
DQS Cleansing 350
Data Quality Services 351
DQS Cleansing Transformation 355
Master Data Management 358
Master Data Services 359
Summary 362
Chapter 11: Incremental Loads in SSIS 363
Control Table Pattern 363
Querying the Control Table 364
Querying the Source Table 366
Updating the Control Table 366
SQL Server Change Data Capture 367
Benefits of SQL Server CDC 368
Preparing CDC 369
Capture Instance Tables 371
The CDC API 372
Using the SSIS CDC Tools 374
Summary 379
Chapter 12: Loading a Data Warehouse 381
Data Profiling 383
Initial Execution of the Data Profiling Task 383
Reviewing the Results of the Data Profiling Task 386
Turning Data Profile Results into Actionable ETL Steps 390
Data Extraction and Cleansing 391
Dimension Table Loading 391
Loading a Simple Dimension Table 392
Loading a Complex Dimension Table 397
Considerations and Alternatives to the SCD Transformation 408
Fact Table Loading 409
SSAS Processing 421
Using a Master ETL Package 426
Summary 428
Chapter 13: Using the Relational Engine 429
Data Extraction 430
SELECT * is Bad 430
WHERE is Your Friend 432
Transform during Extract 433
Many ANDs Make Light Work 437
SORT in the Database 437
Modularize 439
SQL Server Does Text Files Too 440
Using Set-Based Logic 444
Data Loading 446
Database Snapshots 446
The MERGE Operator 448
Summary 452
Chapter 14: Accessing Heterogeneous Data 453
Excel and Access 455
64-Bit Support 455
Working with Excel Files 457
Working with Access 462
Importing from Oracle 469
Oracle Client Setup 469
Importing Oracle Data 470
Using XML and Web Services 472
Configuring the Web Service Task 472
Working with XML Data as a Source 483
Flat Files 486
Loading Flat Files 487
Extracting Data from Flat Files 489
ODBC 491
Other Heterogeneous Sources 494
Summary 495
Chapter 15: Reliability and Scalability 497
Restarting Packages 498
Simple Control Flow 499
Containers within Containers and Checkpoints 501
Variations on a Theme 503
Inside the Checkpoint File 505
Package Transactions 507
Single Package, Single Transaction 508
Single Package, Multiple Transactions 509
Two Packages, One Transaction 511
Single Package Using a Native Transaction in SQL Server 512
Error Outputs 513
Scaling Out 516
Architectural Features 516
Scaling Out Memory Pressures 517
Scaling Out by Staging Data 517
Scaling Out with Parallel Loading 522
Summary 528
Chapter 16: Understanding and Tuning the Data Flow Engine 529
The SSIS Engine 530
Understanding the SSIS Data Flow and Control Flow 530
Handling Workflows with the Control Flow 533
Data Processing in the Data Flow 533
Memory Buffer Architecture 534
Types of Transformations 534
Advanced Data Flow Execution Concepts 543
SSIS Data Flow Design and Tuning 549
Data Flow Design Practices 550
Optimizing Package Processing 555
Troubleshooting Data Flow Performance Bottlenecks 558
Pipeline Performance Monitoring 559
Summary 562
Chapter 17: SS IS Software Development Life Cycle 563
Introduction to Software Development Life Cycles 565
SDLCs: A Brief History 566
Types of Software Development Life Cycles 566
Versioning and Source Code Control 567
Subversion (SVN) 568
Team Foundation Server, Team System, and SSIS 573
Summary 590
Chapter 18: Error and Event Handling 591
Using Precedence Constraints 592
Precedence Constraint Basics 592
Advanced Precedence Constraints and Expressions 593
Event Handling 601
Events 602
Using Event Handlers 603
Event Handler Inheritance 611
Breakpoints 612
Error Rows 616
Logging 622
Logging Providers 622
Log Events 623
Catalog Logging 627
Summary 629
Chapter 19: Programming and Extending SSIS 631
The Sample Components 632
Component 1: Source Adapter 632
Component 2: Transform 633
Component 3: Destination Adapter 634
The Pipeline Component Methods 634
Design-Time Functionality 635
Runtime 639
Connection Time 640
Building the Components 642
Preparation 642
Building the Source Component 648
Building the Transformation Component 660
Building the Destination Adapter 671
Using the Components 679
Installing the Components 679
Debugging Components 680
Design Time 680
Building the Complete Package 682
Runtime Debugging 682
Upgrading to SQL Server 2014 687
Summary 687
Chapter 20: Adding a User Interface to Your Component 689
Three Key Steps for Designing the UI: An Overview 690
Building the User Interface 690
Adding the Project 691
Implementing IDtsComponentUI 693
Setting the UITypeName 697
Building the Form 699
Extending the User Interface 704
Runtime Connections 704
Component Properties 707
Handling Errors and Warnings 708
Column Properties 711
Other UI Considerations 712
Summary 712
Chapter 21: External Management and WMI Task Implementation 715
External Management of SSIS with Managed Code 716
Setting Up a Test SSIS Package for Demonstration Purposes 716
The Managed Object Model Code Library 717
Catalog Management 718
Folder Management 719
Environments 720
The DTS Runtime Managed Code Library 722
SSIS Deployment Projects 722
Parameter Objects 723
Server Deployment 725
Executing SSIS Packages Deployed to the SSIS Catalog 726
Environment References 727
Package Operations 728
Application Object Maintenance Operations 729
Package Operations 729
Package Monitoring 732
Project, Folder, and Package Listing 734
A Package Management Example 735
Package Log Providers 745
Specifying Events to Log 747
Programming to Log Providers 748
SQL Server 2014 Operation Logs 749
Package Configurations 751
Creating a Configuration 752
Programming the Configuration Object 753
Configuration Object 754
Windows Management Instrumentation Tasks 755
WMI Reader Task Explained 755
WMI Data Reader Example 756
WMI Event Watcher Task 762
WMI Event Watcher Task Example 763
Summary 766
Chapter 22: Ad ministering SSIS 767
Using the SSIS Catalog 768
Setting the SSIS Catalog Properties 768
SSISDB 771
Deployment Models 772
Project Deployment Model 773
Package Deployment Model 775
Using T-SQL with SSIS 781
Executing Packages 781
Using Parameters 782
Querying Tables for Parameter Values 783
Using Environments 784
Using Data Taps 789
Creating a Central SSIS Server 790
Clustering SSIS 792
Package Configuration 794
Command-Line Utilities 798
DTExec 798
DTExecUI 799
DTUtil 804
Security 806
Securing the SSIS Catalog 806
Legacy Security 809
Scheduling Packages 811
SQL Server Agent 811
Proxy Accounts 813
64-Bit Issues 814
Monitoring Package Executions 815
Built-in Reporting 815
Custom Reporting 819
Performance Counters 819
Summary 820
Appendix A: SS IS Crib Notes 821
When to Use Control Flow Tasks 821
When to Use Data Flow Transforms 822
Common Expressions and Scripts 824
Appendix B: SS IS Internal Views and Stored Procedures 829
Views 829
Stored Procedures 830
Appendix C: Interviewing for an ETL Developer Position 833
Questions 833
Answers 834
Index 839
WHAT’S IN THIS CHAPTER?
SQL Server Integration Services (SSIS) is the anchor in a growing suite of products that make up the Microsoft SQL Server Business Intelligence (BI) platform. What makes SSIS so important is without the data movement and cleansing features that SSIS brings to the table, the other SQL Server BI products can’t operate. What’s the point of a cube, for example, with bad or inconsistent data? In its simplest form, SSIS is an enterprise-level, in-memory ETL tool. However, SSIS is not just a fancy wrapper around an import wizard. In a drag-and-drop development environment, ETL developers can snap together intricate workflows and out-of-the-box data-cleansing flows that rival custom coding and expensive million-dollar, third-party tools. The best thing about SSIS is that you have already paid for it when you license SQL Server.
When we put together the first edition of this book, we were blown away by the new architecture and capabilities of SSIS. SSIS was a big change from the Data Transformation Services (DTS) product that it replaced, and there was much to learn. Since the first edition of SSIS, we have collectively racked up many years of experience converting older DTS packages and mind-sets over to using it, and trust us when we say that no one who has made the change is asking to go back. We’ve learned some things, too.
While SQL Server 2012 was a large jump forward for SSIS, SQL Server 2014 has some very small iterative changes. When we wrote this book, we dug deeply to mine the decades of cumulative experience working with this product, adding our collective knowledge back into these pages. We hope you will agree that the result makes your experience with SSIS a more productive one. This chapter starts from the beginning by providing an overview of SSIS, describing where it fits within the BI product platform and ETL development in general.
In SQL Server 7.0, Microsoft had a small team of developers work on a very understated feature of SQL Server called Data Transformation Services (DTS). DTS was the backbone of the Import/Export Wizard, and its primary purpose was to transform data from almost any OLE DB–compliant data source to almost any destination. It also had the ability to execute programs and run scripts, making workflow a minor feature.
By the time that SQL Server 2000 was released, DTS had a strong following of DBAs and maybe a few developers. Microsoft included in the release new features like the Dynamic Properties Task that enabled you to alter the package dynamically at runtime. Even though DTS utilized extensive logging along with simple and complex multiphase data pumps, usability studies still showed that developers had to create elaborate scripts to extend DTS to get what they wanted done. A typical use case was enabling DTS to load data conditionally based on the existence of a file. To accomplish this in DTS, you had to use the ActiveX Script Task to code a solution using the file system object in VBScript. The problem with that was DTS lacked some of the common components needed to support typical ETL processes. Although it was powerful if you knew how to write scripting code, most DBAs didn’t have this type of scripting experience (or time).
After five years, Microsoft released the much-touted SQL Server 2005 and SSIS, which was no longer an understated feature like DTS. With the SQL Server 2008 release, SSIS was given extra scalability features to help it appeal more to the enterprise. This is entirely appropriate because so much has been added to SSIS. Microsoft made a huge investment in usability, with simple enhancements to the toolbox that allow newer users to ramp up easier. The main focus of the newest release of SQL Server is on the management and deployment of SSIS.
The scope of the SQL Server 2014 release of SSIS resembles the scope of the SQL Server 2008 R2 release. With the last release of SQL Server 2008 R2, the Microsoft SSIS team did very incremental changes after a very large SQL Server 2008 release. In SQL Server 2012 release, Microsoft had focused on SSIS manageability, making it easier to deploy and execute. Also added in 2012 are robust new data cleansing components that help you standardize and detect data anomalies. Furthermore, improvements to the development tools will help make SSIS developers more productive and help new developers get up to speed more easily. The SQL Server 2014 release uses a newer version of Visual Studio but all in all, it will feel much like SQL Server 2012. You will find new components in SQL Server 2014 SSIS, but they will have to be downloaded from sites like CodePlex from the product team and will eventually be rolled into the core product at a future release.
Most of this book will assume that you know nothing about previous releases of SQL Server SSIS. Instead, it takes a fresh look at SQL Server SSIS. The learning curve can be considered steep at first, but once you figure out the basics, you’ll be creating complex packages in no time. To provide an idea of how easy SSIS is to use, the following section looks at a staple tool in the ETL world: the Import and Export Wizard.
If you need to move data quickly from almost any OLE DB–compliant data source or flat file to a destination, you can use the SSIS Import and Export Wizard (shown in Figure 1-1). In fact, many SSIS packages are born this way, but most packages you wish to keep in a BI solution should not be created with the wizard. The wizard provides a quick way to move data and perform very light transformations of data but does not create packages that use best practices. The wizard is available in all editions of SQL Server except the Local Database edition and Express. It enables you to persist the logic of the data movement into a package file. The basic concept of an import/export wizard has not changed substantially from the days of DTS. You still have the option to check all the tables you want to transfer. In addition, however, you can also encapsulate the entire transfer of data into a single transaction.
FIGURE 1-1
Where do you find the wizard? It depends. If you just need to perform a quick import or export, access the wizard directly from the Start menu by navigating to Start ⇒ Microsoft SQL Server “2014” ⇒ Import and Export Data. The other option is to open a project in the SSIS development environment and select Project ⇒ SSIS Import and Export Wizard. We cover this in detail in Chapter 2. Before we get into all the mechanics for that, see Figure 1-1 for an example of the wizard that has bulk loaded tables.
The SQL Server Data Tools (SSDT) was previously called Business Intelligence Development Studio (BIDS) in SQL Server 2008, and it is the central environment in which you’ll spend most of your time as an SSIS developer. SSDT is just a specialized use of the familiar Visual Studio development environment. In SQL Server 2014, SSDT no longer installs when you install SQL Server. Instead, you’ll have to download and install the SQL Server Data Tools (Business Intelligence for Visual Studio) from the Microsoft website. At the time of this publication, SQL Server 2014 can use the Visual Studio 2012 and 2013 versions to design SSIS packages. Visual Studio can host many different project types, from Console applications to Class Libraries and Windows applications. Although you may see many project types when you create a project, SSDT actually contains project templates for only Analysis Services, Integration Services, Report Server, and variants thereof. SSIS in particular uses a BI project type called an Integration Services project (see Figure 1-2), which provides a development design surface with a completely ETL-based set of tools in the Toolbox window.
FIGURE 1-2
This development environment is similar to the legacy DTS Designer, but the approach is completely different. Most important, this is a collaborative development environment just like any Visual Studio development effort, with full source code management, version control, and multi-user project management. SSIS solutions are developed just like all other .NET development solutions, including being persisted to files — in this case, XML file structures with a .DSTX file extension. You can even develop within the SSDT environment without a connection to a SQL Server instance using the offline mode. Once your solution is complete, it can be built and deployed to one or multiple target SQL servers. These changes from DTS to SSIS are crucial to establishing the discipline and best practices of existing software development methodologies as you develop business intelligence solutions. We’ll discuss this SSDT development interface in more detail in Chapter 2.
Microsoft has truly established SSIS as a major player in the extraction, transformation, and loading (ETL) market. Not only is the SSIS technology a complete code rewrite from SQL Server 2000 DTS, it now rivals other third-party ETL tools that can cost hundreds of thousands of...
Dateiformat: ePUBKopierschutz: Adobe-DRM (Digital Rights Management)
Systemvoraussetzungen:
Das Dateiformat ePUB ist sehr gut für Romane und Sachbücher geeignet – also für „fließenden” Text ohne komplexes Layout. Bei E-Readern oder Smartphones passt sich der Zeilen- und Seitenumbruch automatisch den kleinen Displays an. Mit Adobe-DRM wird hier ein „harter” Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.Bitte beachten Sie: Wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!
Weitere Informationen finden Sie in unserer E-Book Hilfe.
Dateiformat: PDFKopierschutz: Wasserzeichen-DRM (Digital Rights Management)
Das Dateiformat PDF zeigt auf jeder Hardware eine Buchseite stets identisch an. Daher ist eine PDF auch für ein komplexes Layout geeignet, wie es bei Lehr- und Fachbüchern verwendet wird (Bilder, Tabellen, Spalten, Fußnoten). Bei kleinen Displays von E-Readern oder Smartphones sind PDF leider eher nervig, weil zu viel Scrollen notwendig ist. Mit Wasserzeichen-DRM wird hier ein „weicher” Kopierschutz verwendet. Daher ist technisch zwar alles möglich – sogar eine unzulässige Weitergabe. Aber an sichtbaren und unsichtbaren Stellen wird der Käufer des E-Books als Wasserzeichen hinterlegt, sodass im Falle eines Missbrauchs die Spur zurückverfolgt werden kann.
