Building Custom Tasks for SQL Server Integration Services: The Power of .NET for ETL for SQL Server 2019 and Beyond

✍ Scribed by Andy Leonard

• Publisher: Apress
• Year: 2021
• Tongue: English
• Leaves: 698
• Edition: 2
• Category: Library

✦ Synopsis

Build custom SQL Server Integration Services (SSIS) tasks using Visual Studio Community Edition and C#. Bring all the power of Microsoft .NET to bear on your data integration and ETL processes, and for no added cost over what you’ve already spent on licensing SQL Server. New in this edition is a demonstration deploying a custom SSIS task to the Azure Data Factory (ADF) Azure-SSIS Integration Runtime (IR).

All examples in this new edition are implemented in C#. Custom task developers are shown how to implement custom tasks using the widely accepted and default language for .NET development.

Why are custom components necessary? Because even though the SSIS catalog of built-in tasks and components is a marvel of engineering, gaps remain in the available functionality. One such gap is a constraint of the built-in SSIS Execute Package Task, which does not allow SSIS developers to select SSIS packages from other projects in the SSIS Catalog. Examples in this book show how to create a custom Execute Catalog Package task that allows SSIS developers to execute tasks from other projects in the SSIS Catalog. Building on the examples and patterns in this book, SSIS developers may create any task to which they aspire, custom tailored to their specific data integration and ETL needs.

 

What You Will Learn

• Configure and execute Visual Studio in the way that best supports SSIS task development
• Create a class library as the basis for an SSIS task, and reference the needed SSIS assemblies
• Properly sign assemblies that you create in order to invoke them from your task
• Implement source code control via Azure DevOps, or your own favorite tool set
• Troubleshoot and execute custom tasks as part of your own projects
• Create deployment projects (MSIs) for distributing code-complete tasks
• Deploy custom tasks to Azure Data Factory Azure-SSIS IRs in the cloud
• Create advanced editors for custom task parameters

Who This Book Is For

For database administrators and developers who are involved in ETL projects built around SQL Server Integration Services (SSIS). Readers do not need a background in software development with C#. Most important is a desire to optimize ETL efforts by creating custom-tailored tasks for execution in SSIS packages, on-premises or in ADF Azure-SSIS IRs.

✦ Table of Contents

Table of Contents
About the Author
Acknowledgments
Foreword
Author’s First Edition Thoughts
Chapter 1: The Story of This Book
Tribal Knowledge
A Starting Point
The Problem We Are Trying to Solve
Updates to the 2019 Edition
Chapter 2: Preparing the Environment
Creating an Azure DevOps Account
Creating an Azure DevOps Project
Configuring Visual Studio for Source Control
Conclusion
Chapter 3: Creating the Assembly Project
Opening Visual Studio IDE
Adding a Reference
Conclusion
Chapter 4: Check in the Project Code
Initialize Source Control in Azure DevOps
Conclusion
Chapter 5: Signing the Assembly
Preparing to Add a Key
Creating the Key
Applying the Key
Checking In Changes
Conclusion
Chapter 6: Preparing to Build
Using Build Events to Register/Unregister
Finding gacutil.exe
Adding Build Events
Setting the Output Path
Conclusion
Chapter 7: Coding the Task
Using a Reference
Decorating the Class
Inheriting from Microsoft.SqlServer.Dts.Runtime.Task
Adding a Property
Investigating Task Methods
Overriding the Validate Method
Overriding the Execute Method
Conclusion
Chapter 8: Coding a Simple Task Editor
Adding a Task Editor
Adding References
Some Implementation
Adding the User Interface Form
Coding the Form
Coding the Click Event and the Form
Conclusion
Chapter 9: Signing and Binding
Creating a New Public Key Token Value
Signing the Task Editor Project
Binding the Task Editor to the Task
Coding the Task Functionality
Add an Icon
Building the Task
Testing the Task
Conclusion
Chapter 10: Expanding Editor Functionality
Refactoring
Adding and Updating Task Properties
Updating the Execute Method
Adding a New Editor
Complex UI Overview
Adding the Editor Project
Adding References
Using Referenced Assemblies
Conclusion
Chapter 11: Minimal Coding for the Complex Editor
Updating DtsTask Interface Members
Adding Internal Variables
Coding Interface Member Methods
Creating the Editor Form
Adding References
Coding the Form Constructor
Calling the Views
Coding the GeneralView Class
Coding the SettingsView
Commenting Out Exceptions
Conclusion
Chapter 12: Editor Integration
Signing the Assembly
Creating the Key
Sign the ExecuteCatalogPackageTaskComplexUI Assembly
Configure the Build Output Path
Configure Build Events
Editing the ExecuteCatalogPackageTask
Build the Solution
Test the Custom SSIS Task
Conclusion
Chapter 13: Implement Views and Properties
Implementing the GeneralView IDTSTaskUIView Interface
Implementing GeneralView OnInitialize
Implementing GeneralView OnCommit
Coding the GeneralNode
Coding the GeneralNode Properties
Testing GeneralView
Implementing the SettingsView IDTSTaskUIView Interface
Implementing SettingsView OnInitialize
Adding ExecuteCatalogPackageTask Properties
Implementing SettingsView OnCommit for FolderName, ProjectName, and PackageName Properties
Coding SettingsNode for FolderName, ProjectName, and PackageName Properties
Coding the SettingsNode FolderName, ProjectName, and PackageName Properties
Testing SettingsView FolderName, ProjectName, and PackageName Properties
Coding SettingsNode for Connection-Related Members
Regarding SSIS Package Connections
Isolating SSIS Package ADO.Net Connections
Building the ADONetConnections TypeConverter
Surface the SourceConnection Property
Testing the SourceConnection Property
Using the SourceConnection Property
Extracting the ServerName from the SourceConnection
Let’s Test It!
Conclusion
Chapter 14: Implement New Connection
Implementing New Connection Functionality
SourceConnection Property Value Changes
Let’s Test It!
Use Case 1
Use Case 2
Conclusion
Chapter 15: Implement Use32bit, Synchronized, and LoggingLevel SettingsView Properties
Specifying a 32-Bit Interface
Adding the Use32bit Property
Test Use32bit
Adding the Synchronized Property
Examining Execute() Overloads
Building the ExecutionValueParameterSet
Calling the returnExecutionValueParameterSet Function
Adding the Synchronized Property to SettingsView
Test Synchronized
Adding the LoggingLevel Property
Building the LoggingLevel TypeConverter
Extending the ExecutionValueParameterSet with LoggingLevel
Adding the LoggingLevel Property to SettingsView
Test LoggingLevel
Conclusion
Chapter 16: Refactoring SourceConnection
Thinking Azure-SSIS
Refactor Connection Identification
Refactor the SettingsView.SourceConnection Property
Identifying Catalog, Folder, Project, and Package
Adding the returnCatalogProject Method
Adding the returnCatalogPackage Method
Adding the returnCatalog Method
Adding the returnCatalogFolder Method
Let’s Test It!
Conclusion
Chapter 17: Refactoring the SSIS Package Hierarchy
Refactor the SettingsView Catalog, Folder, Project, and Package Properties
The Folder Property and Folders Collection
The Project Property and Projects Collection
The Package Property and Packages Collection
Resetting Collections
Let’s Test Expressions!
Conclusion
Chapter 18: Instrumentation and Validation
Instrumentation
Adding Instrumentation
Let’s Test It!
Validation
Validating the SourceConnection Property
Building the Connection Validation Helper Function
Calling attemptConnection in the Validate Method
How I Tested the attemptConnection Validation
Validating Folder, Project, and Package Properties
Conclusion
Chapter 19: Crushing Bugs
Execution Timeout Expired
Threading, Briefly
ExecuteCatalogPackageTask.Execute
Designing a Test SSIS Package
Implementing a WaitHandle.WaitAny Method
Refactoring logMessage to raiseEvent
Refactoring the Execute Method
Let’s Test It!
Conclusion
Chapter 20: Adding Synchronous Execution Properties
Surfacing the New Properties
Adding MaximumRetries, RetryIntervalSeconds, and OperationTimeoutMinutes
Cleaning Up Outdated Asynchronous Execution
Let’s Test It!
Conclusion
Chapter 21: Testing the Task
Manually Testing Use Cases
Use Cases
Testing Task Existence
Testing Task Validation
Testing the Task Editor
Testing Execution
Conclusion
Chapter 22: Building the Setup Project
Adding the ExecuteCatalogPackageTaskSetup Project
Adding References
Configuring Tags
Configure the Product tag
Configure the Package Tag
Configure the Installation Path Property Tags
Configure the MajorUpgrade and MediaTemplate Tags
Initialize the UIRef and License File Tags
Configure the Installation Feature and Icon
Configure Folders and Folder Structure
The ComponentGroup, Folders, and Folder Structure Fragment
Configure the ExecuteCatalogPackageTask GAC Registration
Configure the ExecuteCatalogPackageTask Tasks Deployment Folder
Configure the ExecuteCatalogPackageTaskComplexUI GAC Registration
Configure the ExecuteCatalogPackageTaskComplexUI Tasks Deployment Folder
Close the ComponentGroup and Folders and Folder Structure Fragment
Rename the Output
Build and Install
Cleaning Up
Execute the Installation
Let’s Test It!
Troubleshooting the Installation
Logged Installation
Cleaning the MSI File
Conclusion
Chapter 23: Using the Execute Catalog Package Task in an SSIS Framework
The Controller SSIS Design Pattern
A Metadata-Driven SSIS Execution Framework
Adding Metadata Database Objects
Add the Execution Engine
Just Add Metadata
Let’s Test It!
Caveats
Conclusion
Chapter 24: Deploying to Azure-SSIS
Examining Package Location Options
Provisioning an Azure SQL Database
Provisioning an Azure Data Factory
Creating and Configuring the Deployment Container
Provision an Azure Storage Account
Create the Deployment Container
Upload the ExecuteCatalogPackageTask.msi File
Create the Main.cmd File
Provisioning an Azure-SSIS Integration Runtime
Reviewing the Installation Log
Conclusion
Chapter 25: Test the Task in Azure Data Factory
Deploying TestSSISProject
Configuring TestSSISProject
Testing the Configuration
Lifting and Shifting an SSIS Framework
Lifting and Shifting Framework Metadata Tables
Lifting and Shifting Framework Metadata
Deploying ECPTFramework SSIS Project
Configure the ECPTFramework SSIS Project
Let’s Test It!
Redmond, We Have a Problem
Analyzing the Results
Conclusion
Chapter 26: Notes from My Experience
Start Visual Studio as an Administrator
Learn How to Recover
Build a Notes File
Cleaning the Solution
Change the Icon File’s Build Action Property
Engineering a Solution for the PackageInfo.Execute Timeout
Engineering a Solution for the Missing xp_msver in Azure SQL DB
Read the Azure-SSIS Custom Task Installation Log
There Are Bugs in the Code
Azure Will Change
Obtain Source Code
Final Thoughts
Index