Professional Microsoft SQL Server 2012 Integration Services
Home > Computing and Information Technology > Databases > Professional Microsoft SQL Server 2012 Integration Services
Professional Microsoft SQL Server 2012 Integration Services

Professional Microsoft SQL Server 2012 Integration Services

|
     0     
5
4
3
2
1




Out of Stock


Notify me when this book is in stock
About the Book

An in-depth look at the radical changes to the newest release of SISS Microsoft SQL Server 2012 Integration Services (SISS) builds on the revolutionary database product suite first introduced in 2005. With this crucial resource, you will explore how this newest release serves as a powerful tool for performing extraction, transformation, and load operations (ETL). A team of SQL Server experts deciphers this complex topic and provides detailed coverage of the new features of the 2012 product release. In addition to technical updates and additions, the authors present you with a new set of SISS best practices, based on years of real-world experience that have transpired since the previous edition was published. Details the newest features of the 2012 SISS product release, which is the most significant release since 2005 Addresses the keys to a successful ETL solution, such as using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements Includes additional case studies and tutorial examples to illustrate advanced concepts and techniques Professional Microsoft SQL Server 2012 Integration Services is a valuable resource that meets the demands and high expectations of experienced SSIS professionals.

Table of Contents:
INTRODUCTION xxix 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 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 Source Component 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 EXPRESSIONS 161 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 Techniques 305 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 Transformation 335 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 Single Package, Multiple Transactions 466 Two Packages, One Transaction 468 Single Package Using a Native Transaction in SQL Server 469 Error Outputs 471 Scaling Out 473 Architectural Features 474 Scaling Out Memory Pressures 474 Scaling Out by Staging Data 475 Scaling Out with Parallel Loading 479 Summary 485 CHAPTER 15: UNDERSTANDING AND TUNING THE DATA FLOW ENGINE 487 The SSIS Engine 488 Understanding the SSIS Data Flow and Control Flow 488 Handling Workfl ows with the Control Flow 491 Data Processing in the Data Flow 491 Memory Buff er Architecture 492 Types of Transformations 493 Advanced Data Flow Execution Concepts 501 SSIS Data Flow Design and Tuning 508 Data Flow Design Practices 508 Optimizing Package Processing 513 Troubleshooting Data Flow Performance Bottlenecks 516 Pipeline Performance Monitoring 518 Summary 520 CHAPTER 16: SSIS SOFTWARE DEVELOPMENT LIFE CYCLE 521 Introduction to Software Development Life Cycles 523 SDLCs: A Brief History 524 Types of Software Development Life Cycles 524 Versioning and Source Code Control 525 Subversion (SVN) 526 Team Foundation Server, Team System, and SSIS 533 Summary 547 CHAPTER 17: ERROR AND EVENT HANDLING 549 Using Precedence Constraints 549 Precedence Constraint Basics 550 Advanced Precedence Constraints and Expressions 551 Event Handling 557 Events 558 Using Event Handlers 560 Event Handler Inheritance 567 Breakpoints 569 Error Rows 572 Logging 576 Logging Providers 577 Log Events 577 Catalog Logging 582 Summary 584 CHAPTER 18: PROGRAMMING AND EXTENDING SSIS 585 The Sample Components 586 Component 1: Source Adapter 586 Component 2: Transform 587 Component 3: Destination Adapter 588 The Pipeline Component Methods 588 Design-Time Functionality 589 Runtime 593 Connection Time 594 Building the Components 596 Preparation 596 Building the Source Component 602 Building the Transformation Component 614 Building the Destination Adapter 625 Using the Components 633 Installing the Components 633 Debugging Components 634 Design Time 635 Building the Complete Package 636 Runtime Debugging 637 Upgrading to SQL Server 2012 641 Summary 641 CHAPTER 19: ADDING A USER INTERFACE TO YOUR COMPONENT 643 Three Key Steps for Designing the UI: An Overview 644 Building the User Interface 644 Adding the Project 645 Implementing IDtsComponentUI 647 Setting the UITypeName 651 Building the Form 653 Extending the User Interface 658 Runtime Connections 658 Component Properties 661 Handling Errors and Warnings 663 Column Properties 665 Other UI Considerations 667 Summary 667 CHAPTER 20: EXTERNAL MANAGEMENT AND WMI TASK IMPLEMENTATION 669 External Management of SSIS with Managed Code 670 Setting Up a Test SSIS Package for Demonstration Purposes 670 The Managed Object Model Code Library 671 Catalog Management 672 Folder Management 673 Environments 674 The DTS Runtime Managed Code Library 676 SSIS Deployment Projects 676 Parameter Objects 677 Server Deployment 679 Executing SSIS Packages Deployed to the SSIS Catalog 680 EnvironmentReferences 681 Package Operations 682 Application Object Maintenance Operations 683 Package Operations 683 Package Monitoring 686 Project, Folder, and Package Listing 688 A Package Management Example 689 Package Log Providers 699 Specifying Events to Log 701 Programming to Log Providers 702 SQL Server 2012 Operation Logs 703 Package Confi gurations 705 Creating a Confi guration 706 Programming the Confi guration Object 707 Confi guration Object 708 Windows Management Instrumentation Tasks 709 WMI Reader Task Explained 709 WMI Data Reader Example 710 WMI Event Watcher Task 715 WMI Event Watcher Task Example 716 Summary 718 CHAPTER 21: USING SSIS WITH EXTERNAL APPLICATIONS 719 InfoPath Documents 720 ASP.NET Applications 727 Winform .NET Applications 731 Executing a Package with T-SQL 736 Summary 741 CHAPTER 22: ADMINISTERING SSIS 743 Using the SSIS Catalog 743 Setting the SSIS Catalog Properties 744 SSISDB 747 Deployment Models 748 Project Deployment Model 749 Package Deployment Model 751 Using T-SQL with SSIS 757 Executing Packages 757 Using Parameters 758 Querying Tables for Parameter Values 759 Using Environments 760 Using Data Taps 765 Creating a Central SSIS Server 766 Clustering SSIS 768 Package Confi guration 770 Command-Line Utilities 774 DTExec 774 DTExecUI 775 DTUtil 780 Security 782 Securing the SSIS Catalog 782 Legacy Security 785 Scheduling Packages 787 SQL Server Agent 787 Proxy Accounts 789 64-Bit Issues 790 Monitoring Package Executions 791 Built-in Reporting 791 Custom Reporting 795 Performance Counters 796 Summary 796 CHAPTER 23: CASE STUDY: A PROGRAMMATIC EXAMPLE 797 What You Will Take Away 798 Background 798 Business Problem 799 Solution Summary 799 Solution Architecture 801 Naming Conventions and Tips 804 Additional SSIS Tips Before You Start a Large Project 805 Data Architecture 805 File Storage Location Setup 806 Bank ACH Payments 806 Lockbox Files 807 PayPal or Direct Credits to the Corporate Account 808 Case Study Database Model 808 Database Setup 810 Case Study Load Packages 819 Bank File Load Package 819 ACH Load Package 845 E-mail Load Package 861 Testing 866 Case Study Invoice Matching Process 867 Matching Process Control Flow 867 Matching Process High-Confidence Data Flow 870 Matching Process Medium-Confidence Data Flow 875 Interpreting the Results 879 Creating a Parent Driver Package 880 Driver Package Setup 880 Summary 881 INDEX 883


Best Sellers


Product Details
  • ISBN-13: 9781118223802
  • Publisher: John Wiley & Sons Inc
  • Binding: Digital (delivered electronically)
  • No of Pages: 960
  • ISBN-10: 1118223802
  • Publisher Date: 14 Mar 2012
  • Language: English


Similar Products

Add Photo
Add Photo

Customer Reviews

REVIEWS      0     
Click Here To Be The First to Review this Product
Professional Microsoft SQL Server 2012 Integration Services
John Wiley & Sons Inc -
Professional Microsoft SQL Server 2012 Integration Services
Writing guidlines
We want to publish your review, so please:
  • keep your review on the product. Review's that defame author's character will be rejected.
  • Keep your review focused on the product.
  • Avoid writing about customer service. contact us instead if you have issue requiring immediate attention.
  • Refrain from mentioning competitors or the specific price you paid for the product.
  • Do not include any personally identifiable information, such as full names.

Professional Microsoft SQL Server 2012 Integration Services

Required fields are marked with *

Review Title*
Review
    Add Photo Add up to 6 photos
    Would you recommend this product to a friend?
    Tag this Book Read more
    Does your review contain spoilers?
    What type of reader best describes you?
    I agree to the terms & conditions
    You may receive emails regarding this submission. Any emails will include the ability to opt-out of future communications.

    CUSTOMER RATINGS AND REVIEWS AND QUESTIONS AND ANSWERS TERMS OF USE

    These Terms of Use govern your conduct associated with the Customer Ratings and Reviews and/or Questions and Answers service offered by Bookswagon (the "CRR Service").


    By submitting any content to Bookswagon, you guarantee that:
    • You are the sole author and owner of the intellectual property rights in the content;
    • All "moral rights" that you may have in such content have been voluntarily waived by you;
    • All content that you post is accurate;
    • You are at least 13 years old;
    • Use of the content you supply does not violate these Terms of Use and will not cause injury to any person or entity.
    You further agree that you may not submit any content:
    • That is known by you to be false, inaccurate or misleading;
    • That infringes any third party's copyright, patent, trademark, trade secret or other proprietary rights or rights of publicity or privacy;
    • That violates any law, statute, ordinance or regulation (including, but not limited to, those governing, consumer protection, unfair competition, anti-discrimination or false advertising);
    • That is, or may reasonably be considered to be, defamatory, libelous, hateful, racially or religiously biased or offensive, unlawfully threatening or unlawfully harassing to any individual, partnership or corporation;
    • For which you were compensated or granted any consideration by any unapproved third party;
    • That includes any information that references other websites, addresses, email addresses, contact information or phone numbers;
    • That contains any computer viruses, worms or other potentially damaging computer programs or files.
    You agree to indemnify and hold Bookswagon (and its officers, directors, agents, subsidiaries, joint ventures, employees and third-party service providers, including but not limited to Bazaarvoice, Inc.), harmless from all claims, demands, and damages (actual and consequential) of every kind and nature, known and unknown including reasonable attorneys' fees, arising out of a breach of your representations and warranties set forth above, or your violation of any law or the rights of a third party.


    For any content that you submit, you grant Bookswagon a perpetual, irrevocable, royalty-free, transferable right and license to use, copy, modify, delete in its entirety, adapt, publish, translate, create derivative works from and/or sell, transfer, and/or distribute such content and/or incorporate such content into any form, medium or technology throughout the world without compensation to you. Additionally,  Bookswagon may transfer or share any personal information that you submit with its third-party service providers, including but not limited to Bazaarvoice, Inc. in accordance with  Privacy Policy


    All content that you submit may be used at Bookswagon's sole discretion. Bookswagon reserves the right to change, condense, withhold publication, remove or delete any content on Bookswagon's website that Bookswagon deems, in its sole discretion, to violate the content guidelines or any other provision of these Terms of Use.  Bookswagon does not guarantee that you will have any recourse through Bookswagon to edit or delete any content you have submitted. Ratings and written comments are generally posted within two to four business days. However, Bookswagon reserves the right to remove or to refuse to post any submission to the extent authorized by law. You acknowledge that you, not Bookswagon, are responsible for the contents of your submission. None of the content that you submit shall be subject to any obligation of confidence on the part of Bookswagon, its agents, subsidiaries, affiliates, partners or third party service providers (including but not limited to Bazaarvoice, Inc.)and their respective directors, officers and employees.

    Accept

    New Arrivals

    Inspired by your browsing history


    Your review has been submitted!

    You've already reviewed this product!