You are previewing Microsoft SQL Server 2012 Integration Services: An Expert Cookbook.
O'Reilly logo
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Book Description

Over 100 expert recipes to design, create, and deploy SSIS packages with this book and ebook

  • Full of illustrations, diagrams, and tips with clear step-by-step instructions and real time examples

  • Master all transformations in SSIS and their usages with real-world scenarios

  • Learn to make SSIS packages re-startable and robust; and work with transactions

  • Get hold of data cleansing and fuzzy operations in SSIS

  • In Detail

    SQL Server Integration Services (SSIS) is a leading tool in the data warehouse industry - used for performing extraction, transformation, and load operations. This book is aligned with the most common methodology associated with SSIS known as Extract Transform and Load (ETL); ETL is responsible for the extraction of data from several sources, their cleansing, customization, and loading into a central repository normally called Data Warehouse or Data Mart.

    Microsoft SQL Server 2012 Integration Services: An Expert Cookbook covers all the aspects of SSIS 2012 with lots of real-world scenarios to help readers understand usages of SSIS in every environment.

    Written by two SQL Server MVPs who have in-depth knowledge of SSIS having worked with it for many years.

    This book starts by creating simple data transfer packages with wizards and illustrates how to create more complex data transfer packages, troubleshoot packages, make robust SSIS packages, and how to boost the performance of data consolidation with SSIS. It then covers data flow transformations and advanced transformations for data cleansing, fuzzy and term extraction in detail. The book then dives deep into making a dynamic package with the help of expressions and variables, and performance tuning and consideration.

    Table of Contents

    1. Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
      1. Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
      2. Credits
      3. Foreword
      4. About the Authors
      5. About the Reviewers
      6. www.PacktPub.com
        1. Support files, eBooks, discount offers and more
          1. Why Subscribe?
          2. Free Access for Packt account holders
          3. Instant Updates on New Packt Books
      7. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Downloading the example code
          2. Errata
          3. Piracy
          4. Questions
      8. 1. Getting Started with SQL Server Integration Services
        1. Introduction
        2. Import and Export Wizard: First experience with SSIS
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Mapping columns
            2. Configure transfer settings for multiple tables
            3. Mapping data types
            4. Querying the source database
          5. See also
        3. Getting started with SSDT
          1. How to do it...
          2. How it works...
        4. Creating the first SSIS Package
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. Getting familiar with Data Flow Task
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. SSIS 2012 versus previous versions in Developer Experience
          1. Getting ready
          2. How to do it...
          3. How it works...
      9. 2. Control Flow Tasks
        1. Introduction
        2. Executing T-SQL commands: Execute SQL Task
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Full result set
            2. XML result set
            3. BypassPrepare
          4. See also
        3. Handling file and folder operations: File System Task
          1. Getting ready
          2. How to do it...
          3. There's more...
        4. Sending and receiving files through FTP: FTP Task
          1. How to do it...
          2. How it works...
          3. There's more...
            1. FTP Connection Manager's advanced options
            2. File Connection Manager
            3. Working with variables
        5. Executing other packages: Execute Package Task
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Advanced options for executing external packages
        6. Running external applications: Execute Process Task
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Reading data from web methods: Web Service Task
          1. How to do it...
          2. How it works...
        8. Transforming, validating, and querying XML: XML Task
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. XPath
              1. Merge
              2. Diff
              3. Patch
            2. See also
        9. Profiling table statistics: Data Profiling Task
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
        10. Batch insertion of data into a database: Bulk Insert Task
          1. Getting ready
          2. How to do it...
          3. How it works...
        11. Querying system information: WMI Data Reader Task
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. WMI code creator—a useful tool for WMI
          5. See also
        12. Querying system events: WMI Event Watcher Task
          1. Getting ready
          2. How to do it...
          3. How it works...
        13. Transferring SQL server objects: DBMS Tasks
          1. Getting ready
          2. How to do it...
          3. How it works...
      10. 3. Data Flow Task Part 1—Extract and Load
        1. Introduction
        2. Working with database connections in Data Flow
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. ADO.NET Source
            2. OLE DB Destination
          4. There's more...
            1. Connect to ODBC data providers
            2. Exploring All properties of connection managers
            3. Advanced Editor
            4. Source and Destination Assistant
            5. Shared Connection Manager
            6. SQL Server Destination
            7. Fast Load option
            8. Querying source data dynamically
          5. See also
        3. Working with flat files in Data Flow
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Passing data between packages—Raw Source and Destination
          1. How to do it...
          2. How it works...
            1. Raw File Destination
            2. Raw File Source
          3. There's more...
            1. Raise performance with Raw Files
        5. Importing XML data with XML Source
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Hierarchical XML data
        6. Loading data into memory—Recordset Destination
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Extracting and loading Excel data
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Data access
            2. Data types
        8. Change Data Capture
          1. Getting ready
          2. How to do it...
          3. How it works...
      11. 4. Data Flow Task Part 2— Transformations
        1. Introduction
        2. Derived Column: adding calculated columns
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Audit Transformation: logging in Data Flow
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Aggregate Transform: aggregating the data stream
          1. How to do it...
          2. How it works...
        5. Conditional Split: dividing the data stream based on conditions
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Lookup Transform: performing the Upsert scenario
          1. Getting ready
          2. How to do it...
          3. How it works...
              1. Specify how to handle rows with no matching entries
              2. Connection tab
              3. Columns tab
              4. Two different outputs
          4. There's more...
            1. Cache mode
            2. Connection type
        7. OLE DB Command: executing SQL statements on each row in the data stream
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. OLE DB
            2. Parameter markers
          4. There's more...
            1. Fetching the output parameter into the data stream column
        8. Merge and Union All transformations: combining input data rows
          1. Getting ready
          2. How to do it...
          3. How it works...
        9. Merge Join Transform: performing different types of joins in data flow
          1. How to do it...
          2. How it works...
            1. Joining key(s)
            2. Join types
          3. There's more...
            1. Performance issue with Sort Transformation
              1. Order by clause
              2. IsSorted
              3. SortKeyPosition
            2. Merge Join versus Lookup
            3. Sort Transform properties
              1. Remove rows with duplicate sort values
              2. Sort Order
              3. Sort Type
              4. Comparison Flags
              5. Pass Through
        10. Multicast: creating copies of the data stream
          1. Getting ready
          2. How to do it...
          3. How it works...
        11. Working with BLOB fields: Export Column and Import Column transformations
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Export Column
            2. Import Column
        12. Slowly Changing Dimensions (SCDs) in SSIS
          1. Getting ready
          2. How to do it...
            1. How it works...
      12. 5. Data Flow Task Part 3—Advanced Transformation
        1. Introduction
        2. Pivot and Unpivot Transformations
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Pivot Transformation
            2. Unpivot Transformation
          4. There's more...
            1. Pivot Transformation; Working with the Advanced Editor
        3. Text Analysis with Term Lookup and Term Extraction transformations
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Excluding Terms
        4. DQS Cleansing Transformation—Cleansing Data
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. The DQS Cleansing Component acts asynchronously
            2. Advanced settings
            3. Status Column
        5. Fuzzy Transformations—how SSIS understands fuzzy similarities
          1. Getting ready
          2. How to do it...
          3. How it works...
      13. 6. Variables, Expressions, and Dynamism in SSIS
        1. Introduction
        2. Variables and data types
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Using expressions in Control Flow
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. DelayValidation
            2. IsVariable
            3. Real-world scenarios
        4. Using expressions in Data Flow
          1. Getting ready
          2. How to do it...
          3. How it works...
        5. The Expression Task
          1. How to do it...
          2. How it works...
          3. There's more...
            1. Alternative method
        6. Dynamic connection managers
          1. Getting ready
          2. How to do it...
          3. How it works...
        7. Dynamic data transfer with different data structures
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Other sources and destinations
              1. Flat file to SQL server
              2. SQL query to flat file
            2. SQL server to SQL server on the same server
              1. Excel to SQL server
              2. Other DB engines with SQL server
              3. Any other source and destination
      14. 7. Containers and Precedence Constraints
        1. Introduction
        2. Sequence Container: putting all tasks in an executable object
          1. How to do it...
          2. How it works...
        3. For Loop Container: looping through static enumerator till a condition is met
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Foreach Loop Container: looping through result set of a database query
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. ADO Enumerator
            2. Variable Mappings
            3. Type Cast in expressions in the derived column
        5. Foreach Loop Container: looping through files using File Enumerator
          1. Getting ready
          2. How to do it...
          3. How it works...
        6. Foreach Loop Container: looping through data table
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. DelayValidation
        7. Precedence Constraints: how to control the flow of task execution
          1. How to do it...
          2. How it works...
            1. Types of Constraints
            2. Evaluation operation
            3. Multiple constraints
      15. 8. Scripting
        1. Introduction
        2. The Script Task: Scripting through Control Flow
          1. How to do it...
          2. How it works...
            1. ReadOnlyVariables
            2. ReadWriteVariables
            3. Script
            4. Code description
            5. Script results
            6. Fire Events
        3. The Script Component as a Transformation
          1. How to do it...
          2. How it works...
            1. Script
          3. See also
        4. The Script Component as a Source
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. CreateNewOutputRows
            2. AddRow
          4. See also
        5. The Script Component as a Destination
          1. How to do it...
          2. How it works...
        6. The Asynchronous Script Component
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. SynchronousInputID
            2. InputName_ProcessInput
          4. There's more...
            1. Script component methods
              1. AcquireConnection
              2. Log
              3. PostExecute
              4. PreExecute
              5. ProcessInput
              6. ReleaseConnection
      16. 9. Deployment
        1. Introduction
        2. Project Deployment Model: Project Deployment from SSDT
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Using Integration Services Deployment Wizard and command-line utility for deployment
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. ISPAC file
            2. Integration Services Deployment Wizard graphical user interface
            3. Integration Services Deployment Wizard command-line utility
        4. The Package Deployment Model, Using SSDT to deploy package
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. File system
            2. SQL Server
            3. SSIS package store
        5. Creating and running Deployment Utility
          1. How to do it...
          2. How it works...
            1. Deployment Utility Ingredients
        6. DTUTIL—the command-line utility for deployment
          1. How to do it...
          2. How it works...
          3. There's more...
            1. DTUTIL exit codes
            2. Multiple packages deployment
        7. Protection level: Securing sensitive data
          1. How to do it...
          2. How it works...
            1. Sensitive data
            2. Protection level types
              1. Do Not Save Sensitive
            3. Encrypt Sensitive with User Key
            4. Encrypt Sensitive with Password
            5. Encrypt All with User Key
            6. Encrypt All with Password
            7. Rely on Server Storage
          3. There's more...
            1. The ProtectionLevel property of a package
            2. Protection level in project properties
            3. Protection level in DTUTIL
      17. 10. Debugging, Troubleshooting, and Migrating Packages to 2012
        1. Introduction
        2. Troubleshooting with Progress and Execution Results tab
          1. Getting ready
          2. How to do it...
            1. How it works...
        3. Breakpoints, Debugging the Control Flow
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. How to enable breakpoints
            2. Breakpoints window
            3. Breakpoint menu options
            4. Monitoring windows
            5. Breakpoints limitation in SSIS
            6. Script breakpoint support
        4. Handling errors in Data Flow
          1. Getting ready
          2. How to do it...
            1. How it works...
              1. Error columns and understanding them
        5. Migrating packages to 2012
          1. How to do it...
          2. How it works...
        6. Data Tap
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Create the execution
            2. Create the Data Tap
            3. Running the package
      18. 11. Event Handling and Logging
        1. Introduction
        2. Logging over Legacy Deployment Model
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Logging over Project Deployment Model
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. The SSISDB database
            2. The SSIS dashboards
            3. Some useful SSIS Catalog features
                1. 1 SSISDB
              1. 2 PacktPub
              2. 3 Projects
              3. 4 R02_Project Deployment Mode
        4. Using event handlers and system variables for custom logging
          1. Getting ready
          2. How to do it...
            1. Traditional approach
            2. Project Deployment Mode
          3. How it works...
          4. There's more...
            1. The traditional approach—Legacy Deloyment Model
            2. The new approach—Project Deployment Model
              1. Enriching default views
              2. Get row counts
              3. Custom reporting services reports
      19. 12. Execution
        1. Introduction
        2. Execution from SSMS
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Logging level
            2. Package validation
            3. Execution and validation reports
            4. Legacy package execution from SSMS
        3. Execution from a command-line utility
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. DTEXEC
          4. There's more...
            1. Execution with DTExecUI
            2. Execution with SSIS catalog procedures
            3. 32-bit / 64-bit issue
        4. Execution from a scheduled SQL Server Agent job
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Running SQL Server Agent job under a proxy account
            2. Creating an SQL Server job more easily
      20. 13. Restartability and Robustness
        1. Introduction
        2. Parameters: Passing values to packages from outside
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. Environment
        3. Package configuration: Legacy method to inter-relation
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. XML configuration file
            2. SQL server
            3. Environment variable
            4. Registry entry
            5. Parent package variable
          4. There's more...
            1. Indirect configuration
            2. Configuration priority
        4. Transactions: Doing multiple operations atomic
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Required
            2. Supported
            3. NotSupported
        5. Checkpoints: The power of restartability
          1. Getting ready
            1. How to do it...
            2. How it works...
              1. CheckpointFileName
              2. FailPackageOnFailure
              3. CheckpointUsage
              4. SaveCheckpoints
        6. SSIS reports and catalog views
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
            1. SSIS Catalog views
            2. Simple catalog views
      21. 14. Programming SSIS
        1. Introduction
        2. Creating and configuring Control Flow Tasks programmatically
          1. Getting ready
          2. How to do it...
          3. How it works...
        3. Working with Data Flow components programmatically
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Executing and managing packages programmatically
          1. Getting ready
          2. How to do it...
          3. How it works...
            1. Saving packages
            2. Loading packages
          4. There's more...
            1. Methods of the Application class
            2. Set parameter's value programmatically
        5. Creating and using Custom Tasks
          1. How to do it...
          2. How it works...
            1. Signing the project
            2. Adding assembly to GAC
          3. There's more...
            1. Creating custom Data Flow component
            2. Creating UI Editor for custom object
      22. 15. Performance Boost in SSIS
        1. Introduction
        2. Control Flow Task and variables considerations for boosting performance
          1. How to do it...
            1. Using SSISDB Catalog
            2. Progress Bar
            3. Windows Performance Monitor
            4. BIDS Helper
          2. How it works...
        3. Data Flow best practices in Extract and Load
          1. Getting ready
          2. How to do it...
            1. Optimize Queries
            2. OLE DB Destination
            3. Data conversions
            4. Update data into destination
          3. How it works...
          4. There's more...
        4. Data Flow best practices in Transformations
          1. Getting ready
          2. How to do it...
            1. Remove unused columns from the pipeline
            2. Avoid unnecessary sorting in the pipeline
            3. Lookup reference data
            4. Make use of the Cache Transformation to store data in memory
          3. How it works...
          4. There's more...
            1. Replace lookup by changing SQL statement at the source
        5. Working with buffer size
          1. How to do it...
          2. How it works...
        6. Working with performance counters
          1. How to do it...
          2. How it works...