Custom Runtime Components

Integration Services supports five different custom component types; however, this book only covers how to write custom tasks and data flow components in detail because those are the most commonly required. The other custom components you can write are as follows:

• Foreach Enumerators

• Connection managers

• Log providers

By now, you know that Foreach Enumerators are the components that plug into the ForEach Loop. They iterate over collections of objects and provide an element or collection of elements on each Foreach Loop iteration. Because the stock Foreach Enumerators are very flexible and provide a way to iterate over SQL Server Management Objects (SMO) objects, static text, ADO recordsets, schema rowsets, files, nodelists, or even generic .NET collections, there doesn't seem to be a lot of demand for custom Foreach Enumerators. However, if you want to write one, you can.

The stock connection managers give pretty good coverage for database connections, but if you need to access an unsupported protocol, server, or other resource, for example, Lightweight Directory Access Protocol (LDAP), Simple Object Access Protocol (SOAP), or build an enhanced File Transfer Protocol (FTP) Connection Manager, you can write a custom connection manager.

Log providers are extensible as well and provide a layer between the package logging infrastructure and the destination logging medium. Here again, the stock log providers provide pretty good coverage, including SQL Server, text, XML, SQL Profiler, and Windows event logging.

Only a brief introduction to these custom component types is provided here because they share common setup with custom tasks and data flow components. Books Online provides excellent resources if you want to further pursue writing one of these component types.

Requirements for Building Custom Components

Before digging into the nuts and bolts of developing the components, let's talk a little about the requirements. You should be familiar with the following tools and have them installed. If you have Visual Studio installed, they should already be available. Also, if you have never used these tools, you should become familiar with them and understand the basics of how they function.

Development Platform

Visual Studio is the preferred platform, but any environment that supports .NET will do, for example Borland Delphi or #develop will also work. It is important that the tools are compatible with .NET Framework 2.0.

Debugging Environment

Whatever platform you choose, it should have a development environment. For this chapter, the Visual Studio development and debugging environment is assumed. If you're using a different environment, you'll need to extrapolate the instructions to match the environment.

Strong Name Utility and GACUtil

All Integration Services custom components must be placed in the Global Assembly Cache (GAC) and so need to be strongly named. It is best if the Strong Name Utility (sn.exe) is on the path so that it can be executed from any directory. The same goes for GACUtil.exe. You'll use this utility for adding and possibly removing assemblies to the GAC.

Programming Languages Supported

You can use any .NET language to write custom components for Integration Services, including VB.NET, COBOL.NET or Delphi.NET. You can even write custom components in C++, although Microsoft does not support it. However, the examples in this chapter are in C# because that is the language used to write the managed stock components. For VB.NET developers, search Books Online for "custom task" to find a wealth of sample code you can reference.

Computer Needed

As in previous chapters, you'll not fully understand the code presented here until you actually work through the samples on your machine, step through it in the debugger, and so on. There are numerous aspects to developing components and only a few can be documented here. Working through the samples is the best way to understand this chapter.

The Sample Tasks

There are four sample tasks in the samples folder. Each is in various stages of development so you can see what is possible. This chapter includes parts of those tasks when needing to explain certain concepts. Studying the code for these sample tasks in detail is a great way to quickly get up to speed on writing your own. The tasks are as follows:

• StarterTask This is a sample task with a fully functional task user interface (task UI) you can use as a starting point for writing your own custom tasks. The task UI uses the same task UI infrastructure as the stock tasks. The task has two properties of type integer and string. The properties serve no purpose but to illustrate how to add properties and support them in the UI.

• HTTPTask This is a stripped-down custom task for downloading HTML pages. It has no user interface and is very rudimentary. The HTTPTask is the kind of task someone might write for internal use only to eliminate the continual copying and pasting of Script Tasks or to make task configuration easier. It shows how easy it is to create a functional custom task without a lot of effort.

• ExpressionTask This is a simple task that supports assigning values to variables through expressions using the SSIS Expression Evaluator. This task is useful in situations in which you need to modify the value of a variable and don't want to use a Script Task. For example, you can use this task as a way to prematurely break out of a For Loop based on workflow.

• CryptoTask This is a fully functional task and task UI similar to the stock tasks. It provides the ability to encrypt and decrypt text files with a password. It also has the kind of validation, with warnings and errors, that you would expect from a fully functional task.

Deciding to "Roll Your Own"

Any discussion about writing custom components wouldn't be complete without some mention of the criteria for deciding when you should do so. For example, how do you decide if it is better to invest time in a Script Task or write a custom task instead? The following list offers a few guidelines to follow when deciding to write a custom component:

• Resurrecting legacy code You might have legacy business rules or other code that you want to reuse. Rewriting might not be an option, but the code has well-understood interfaces or call points. Calling these methods from a Script Task might be cumbersome and repetitive. Wrapping up the calls in a component abstracts away the details of the legacy code.

• Simplifying code reuse If you plan to use the logic for the component in multiple packages and don't want to copy and paste to multiple locations, writing a custom component is the right answer.

• Hiding intellectual property There is no way to hide code in the Script Task or script component. If the algorithm you are writing is sensitive or you simply want to hide the details of the implementation from those who might innocently break the code, you should write a custom component.

• Selling components If you want to write components to sell, you, of course, need to write the component.

• Dealing with complex problems If the logic and code you need to implement is even slightly complex, you are likely better off writing a custom component so that you can break up the code into more manageable units.

• Getting better performance Although the Script Task and components are pretty performant, there is no good way to profile or optimize them.

• Supporting an unsupported protocol If you want support for an existing or new unsupported protocol, you need to write a custom connection manager and possibly other components as well. The Script Task or component cannot be used to create any of the other three component types.

It's also important to understand when you shouldn't roll your own. The following list enumerates a few reasons why you wouldn't write a custom component:

• Similar component exists This is likely obvious, but true nonetheless. When a similar component already exists that will do what you need, why write a new one? What isn't so obvious is what components are already available. To find out more about available components, go to the Community menu in Visual Studio and select the Partner Products Catalog menu option, which takes you to the Visual Studio Industry Partners Product Catalog website.

• Function achievable by combining existing components Integration Services is designed with the toolbox approach. Components provide slices of well-defined behavior that you combine together to build functionality. Sometimes, it is easy to overlook the power of this approach and think that a particular task simply cannot be performed with the existing toolset. Before starting a custom component, give some consideration to how you can accomplish the given function with a clever combination of existing tools.

• Function only needed for the short term or in few packages If the feature or function you need will only be useful for a short time, is just a proof of concept, or will only be used in a few packages, your efforts would probably be better spent writing the function in a Script Task or component.

• Function available in an executable If you can spawn a command-line executable to do the same thing as your required function, consider doing that instead.

There are a lot of considerations and trade-offs to make when deciding to write a custom component. The listed guidelines are just a start and should be considered along with the myriad of other considerations that aren't listed, such as scheduling and budgetary constraints, long-term maintenance, area expertise, and future project viability expectations that are specific to a given project. Hopefully, these guidelines will get you started with your decision.