SQL Server Integration Services (SSIS) is a much improved product over its predecessor, Data Transformation Services (DTS), and there are many reasons that its adoption as a data manipulation/ETL tool continues to increase.  When I am asked to justify why I think SSIS should be the tool of choice for an ETL, I like to point out that, at its core, SSIS really just represents a great workflow engine that incorporates much of the standard instrumentation that any ETL requires. Its integration with SQL Agent to schedule and monitor jobs seals the deal.

Out of the box, SSIS ships with some 80+ components that are designed to address work items that are involved in typical data manipulation operations. For we developer types, some of the tasks are great, some are good, and some are not so good. Before we criticize too harshly though, we must keep in mind that these tasks were developed with the non-programmer type in mind. In those cases where one thinks that he can do better or there just isn't an existing task to handle a requirement, SSIS enables access to the .NET platform for custom code to be executed via the Script Task and Script Component items. This means that virtually anything can be accomplished using SSIS.

With custom coding comes a responsibility for minimizing future maintenance nightmares though. As with any other type of development, just about the time a developer finds him/herself copying & pasting a code snippet (or task, in the case of SSIS), it's time to think about re-factoring the code base to prevent redundancy and preclude any need to make the same code change in multiple places. This is when the ability to extend SSIS through the creation of custom libraries and custom task components really becomes valuable.

In cases where there is no need for a task to support custom configuration, it may make sense to simply centralize the shared code in a custom assembly and then deploy that assembly to the GAC for use by script tasks/components. I've taken this approach in the past when a process was expected to manipulate a set of records to apply a series of complex rules but the set of records came from different data sources based on different filter criteria. Ultimately, the process was the same so all that changed was the source of the data that was used to feed the business rules engine. In these cases, a data source task fed a script component which delegated the rules processing to the custom classes and returned the data to the process flow accordingly.

In other cases, though, we find ourselves with the opportunity/need to create a generic, reusable task control that can be dynamically configured and used in mutiple packages. Here I offer a few of the tasks that I've added to my toolbox. I will continue to increase the size of this tool kit over time and will continue to update the downloads.

Currently, the following tasks are included with several others in the pipeline in various different stages of completion. Click on an image to learn more about the task control.

TODO:

additional text here...

ReadMe file

Usage Document

Installation Instructions