Extending SSIS with .NET Scripting: A Toolkit for SQL Server Integration Services

Extending SSIS with .NET Scripting: A Toolkit for SQL Server Integration Services

Released October 2015
Publisher(s): Apress
ISBN: 9781484206386

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Extending SSIS with .NET Scripting is a timeless and comprehensive scripting toolkit for SQL Server Integration Services to solve a wide array of everyday problems that SSIS developers encounter. The detailed explanation of the Script Task and Script Component foundations helps you develop your own scripting solutions, but this book also shows a broad arsenal of readymade and well-documented scripting solutions for common problems. All examples are in both C# and VB.NET, and work for all current versions of SSIS.

SSIS is one of the leading ETL, Data Consolidation, and Data Transformation tools in today’s market. SSIS is used by ETL Developers, DBAs and Data Analysts to transform data as required for different ETL processes. There are many built-in components and tasks to help developers to perform actions. For example, there are tasks for sending and receiving files through FTP, sending an email, and for accessing a wide range of database management systems. Yet there are times when developers require a task or component that does not exist and it would make their life much easier if they could create that task or component, and that is what this book it is about. It shows how to write .NET scripts and use the powerful Microsoft .NET library to implement new functionality as needed.

  • Provides a timeless scripting toolkit for all current SSIS versions
  • Gives a comprehensive explanation of scripting in SSIS
  • Offers a wide array of readymade examples for everyday problems
    •

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Contents at a Glance
    5. Contents
    6. About the Authors
    7. About the Technical Reviewer
    8. Acknowledgments
    9. Introduction
    10. Part I: Getting Started
      1. Chapter 1: Getting Started with SSIS and Scripting
        1. Performing a Basic Action with SSIS Built-in Components
          1. What Is SSIS?
          2. Development Tool
          3. Control Flow Tasks
          4. Data Flow Components
          5. Example 1: Load .csv File into Database
        2. Performing an Action with a Combination of SSIS Built-in Components
          1. Example 2: Find Files in a Folder and Load Them into the Database with Built-in Components
        3. .NET Scripting Makes Life Easier
          1. Example 3: Find the Latest Modified .csv File
        4. SSIS Versions and .NET Library Limitations
        5. Programming Language: C# or VB.NET
        6. Summary
      2. Chapter 2: Script Task vs. Script Component
        1. Introduction to Script Task
          1. Package Design
          2. Edit Script Task
          3. The Script
          4. Testing
        2. Introduction to Script Component
          1. Flat File
          2. Script Component
        3. When to Use a Script Task/Component
        4. Building Code
        5. Debugging in Visual Studio
          1. Script Task
        6. Script Component
        7. Summary
      3. Chapter 3: .NET Fundamentals
        1. Introduction
        2. .NET Data Types .vs Data Flow Data Types
        3. Data Types
        4. Variables
        5. Operators
        6. Using/Import, Classes, and Namespaces
        7. Arrays
          1. The Different Types of Arrays
          2. Initializing Arrays
          3. Accessing Array Members
        8. Collections
        9. Lists
        10. Generics
        11. Loops
          1. The while Loop
          2. The for Loop
          3. The foreach Loop
        12. Error Handling
          1. The throw Statement
          2. The try-catch Statement
          3. The try-catch-finally Statement
        13. Summary
    11. Part II: Script Tasks
      1. Chapter 4: Script Task
        1. Editor
        2. Script Layout
        3. Variables and Parameters
          1. Method 1: ReadOnlyVariables and ReadWriteVariables
          2. Method 2: Variable Dispenser
        4. Referencing Assemblies
          1. Creating an Assembly
        5. Connection Managers
          1. File Connection Managers
          2. Logging Events
        6. FireCustomEvents
        7. Child Package
        8. The Script
        9. The Parent Package
          1. The Script Task
        10. The Code
        11. Summary
      2. Chapter 5: File Properties
        1. Getting All Properties
          1. Checking for File Existence
          2. File Name, Extension, and Path
          3. File Created and Modified Time
          4. File Owner
          5. File Attributes and ReadOnly
          6. File Size
          7. Examples of the File Properties Mentioned
        2. Deleting Files Older Than X Days
        3. Checking for a Locked File
        4. Moving the File
        5. Foreach Loop Ordered File Enumerator
          1. Foreach Ordered File Enumerator
          2. Creating a Dataset
          3. Loading Files
          4. Adding Rows to the Dataset
          5. Writing the Result
          6. Putting It All Together
        6. Summary
      3. Chapter 6: Working Through the Internet and the Web
        1. Sending HTML-Formatted Email
          1. SMTP Connection Manager
          2. Variables
          3. Script Task
          4. The Code
          5. The Results
        2. Downloading a File from a Web Server
          1. Data Flow Task
          2. HTTP Connection Manager
          3. Script Task
          4. The Code
          5. The Results
        3. Downloading the Latest File from an FTP Server
          1. Variables
          2. The Results
        4. Downloading a File from an SFTP Server
          1. Download and Install
          2. Variables
          3. Script Task
          4. Add Reference
          5. The Code
          6. The Results
        5. Summary
      4. Chapter 7: Working with Web Services and XML
        1. Windows Communication Foundation
        2. Web Services
          1. Creating Variables
          2. The Script
          3. Service Reference
          4. Custom Namespace
          5. Config or Not Config
          6. Solution 1: Modifying .config Files
          7. Solution 2: The In-Code Method
          8. The Foreach Loop to Handle XML
          9. Variable Mappings
        3. Validating XML Against Schemas
          1. Validating XML with Schemas
          2. Connections
          3. The Code
          4. Custom Namespaces
          5. Validating the XML
        4. Summary
      5. Chapter 8: Advanced Solutions with Script Task
        1. Regular Expressions
          1. Variables
          2. Script Task
          3. The Script
          4. Precedence Constraint
          5. Testing
        2. Zip/Unzip
          1. Downloading the Library
          2. Unzipping
          3. Adding a Reference
          4. The Unzip Script
          5. Zipping
          6. Reference
          7. The Zip Script
        3. Encrypt/Decrypt Files
          1. Connection Managers
          2. Variable
          3. Script Task
          4. The Script
        4. Summary
    12. Part III: Script Component
      1. Chapter 9: Script Component Foundation
        1. Editor
          1. Input Columns
          2. Inputs and Outputs
          3. Connection Managers
          4. Script Layout
        2. Variables and Parameters
          1. Method 1: ReadOnlyVariables and ReadWriteVariables
          2. Method 2: Variable Dispenser
          3. Conclusion
        3. Connection Managers
        4. Logging Events
        5. Changing .NET Versions
        6. Summary
      2. Chapter 10: Script Component As Source
        1. Flat File with an Uneven Number of Columns
          1. Script Component Source
          2. Creating Output Columns
          3. Creating a File Connection Manager
          4. The Code
          5. The Results
        2. Flat File with Records Split over Multiple Rows
          1. Script Component Source
          2. Output Columns
          3. Create a Connection Manager
          4. The Code
          5. The Results
        3. Generate Random Data with the Script Component As a Source
          1. Script Component Source
          2. Output Columns
          3. The Code
          4. The Results
        4. Summary
      3. Chapter 11: Script Component Transformation
        1. Script Component Transformation
        2. ProperCase
          1. The Source
          2. The Script Transformation
        3. Encrypting and Decrypting Data
          1. The Solution Package
          2. Variables
          3. The Script
          4. Salting the Password
          5. Encrypting the Stream
          6. Encrypting Data
          7. Decrypting Data
        4. Comparing Rows
        5. IsNumeric
        6. Creating Surrogate Keys
        7. Creating GUIDS
          1. Creating the Package
          2. Add Script Component
          3. The Results
        8. Conditional Multicast
          1. The Data Source
          2. The Script
        9. Summary
      4. Chapter 12: Script Component As Destination
        1. Basic Flat File Destination with Header and Footer
          1. Create a File Connection Manager
          2. Source
          3. Script Component
          4. Adding Input Columns
          5. Selecting Connection Manager
          6. The Code
          7. The Results
        2. Basic XML Destination
          1. Creating a File Connection Manager
          2. Source
          3. Script Component
          4. Input Columns
          5. Selecting Connection Manager
          6. The Code
          7. The Results
        3. Summary
      5. Chapter 13: Regular Expressions
        1. Prerequisites
        2. Validating Email Addresses
          1. The Script
          2. Calling the Method
        3. Removing HTML Tags
          1. The Package
          2. The Script
        4. Cleaning/Validating
          1. The Package
          2. The Script
        5. Summary
      6. Chapter 14: Script Component Reflection
        1. Flexible XML Destination
          1. The Script
          2. The Results
        2. Transformation of All Columns
          1. Script Component Type
          2. Script Component Input Columns
          3. The Script
          4. The Results
        3. Calculating a Hash for a Row
          1. Variables
          2. Script Component
          3. ReadOnly Variable
          4. Input Columns
          5. Output Columns
          6. The Script
          7. The Results
        4. Summary
      7. Chapter 15: Web Services
        1. Enriching with Weather
          1. Variables
          2. Script Component
          3. Input Columns
          4. Inputs and Outputs
          5. Add References
          6. Add Service Reference
          7. Namespaces
          8. Class Variables
          9. PreExecute
          10. Input0_ProcessInputRow
          11. PostExecute
          12. The Results
        2. Dynamics CRM
          1. Data Flow Example 1
          2. Data Flow Example 2
          3. Download CRM SDK
          4. Windows Identity Foundation
          5. Variables
          6. Add Script Component
          7. Input Columns
          8. Add References
          9. Add Namespaces
          10. Class Variables
          11. PreExecute
          12. Input0_ProcessInputRow
        3. SharePoint
          1. Variables
          2. Script Component
          3. The Script
          4. Extracting Excel Files from the Libraries
          5. PreExecute
          6. Input0_ProcessInputRow
        4. Summary
    13. Part IV: Custom Tasks and Components
      1. Chapter 16: Create a Custom Task
        1. Custom Task Preparations
          1. Creating Visual Studio Projects
          2. Adding SSIS References
          3. Default Namespace and Assembly Name
          4. Creating a Key for a Strong Name
          5. Getting the Public Key Token
          6. Icons
          7. Build Events
        2. Custom Task Runtime Code
          1. Task Properties
          2. Validating Task
          3. Execution Code
          4. SaveToXML and LoadFromXml
        3. Custom Task Form
        4. Form Code
          1. TaskHost and ServiceProvider
          2. PageLoad
          3. Interface Class Code
        5. Expression Builder
          1. References
          2. Usings
          3. Form Controls
          4. The Code
        6. Summary
      2. Chapter 17: Create Custom Transformation
        1. Custom Transformation Preparations
          1. Creating Visual Studio Projects
          2. Adding SSIS References
          3. Default Namespace and Assembly Name
          4. Creating a Key for the Strong Name
          5. Getting the Public Key Token
          6. Icons
          7. Build Events
        2. Custom Transformation Runtime Code
          1. Component Properties and Input and Output Ports
          2. Validating Transformation
          3. Execution Code
          4. PreExecute
          5. PerformUpgrade
          6. Disable Advanced Editor
          7. Custom Transformation Form
        3. Form Code
          1. ServiceProvider, Connections, Variables, and Metadata
          2. FormLoad
          3. Interface Class Code
        4. Summary
    14. Part V: Scripting from .NET Applications
      1. Chapter 18: Package Creation
        1. Creating an SSIS Package Programmatically
        2. Creating an SSIS Package with BIMLScript
          1. Creating Stage Database
          2. Adding a New BIML File
          3. Adding .NET Code
        3. Adding a Script Component with a RowNumber
        4. BIMLScript: Master Package
        5. Summary
      2. Chapter 19: Package Execution from .NET
        1. Package Deployment Model
          1. Add Reference
          2. Create a Form
          3. The Code
          4. Logging
          5. The Results
        2. Project Deployment Model
          1. Add Reference
          2. Create a Form
          3. The Code
          4. The Results
        3. Summary
    15. Index

    Product information

    • Title: Extending SSIS with .NET Scripting: A Toolkit for SQL Server Integration Services
    • Author(s):
    • Release date: October 2015
    • Publisher(s): Apress
    • ISBN: 9781484206386