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.
|
