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

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