SQL Server Integration Services (SSIS), Microsoft's replacement in SQL Server 2005 for Data Transformation Services (DTS), comes loaded with a broad spectrum of components for importing data and turning it into a living thing, not just something to be passively imported. But there may come a point where you find even the new spectrum of SSIS tools doesn't quite cover all the jobs you might need to do.
To that end, Microsoft provided two basic ways to extend SSIS's functionality. One method is comparatively simple for people who don't have much programming experience or don't need to write anything terribly complex; and another is sophisticated and allows a more ambitious programmer to hook deeply into SSIS and expand enormously on it.
The Simple Approach: Scripting
Most of us are probably at least passingly familiar with scripts in other contexts, and SSIS uses VB .NET to allow a programmer to script actions within an SSIS package. The scope of a script, as opposed to a custom object, is deliberately small and focused; it's for when you need something done more or less within the context of what an existing package will allow or already does.
Within an SSIS package there are two elements you can use to add scripting: the Script Task (in the Control Flow window of the Integration Services Designer application) and the Script Component (in the Data Flow window). Each is best used in slightly different circumstances.
The Script Task is something you'll want to use for general-purpose Flow Control in a package — it's more global and powerful than the Script Component, but also that much more complex. It runs outside the Data Flow of the package and isn't constrained by the way the Data Flow works, although the Script Task generally only runs when a package is tr...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
iggered (although you can build in exceptions). The Task also supports breakpoints and debugging, which is useful if you're writing a fairly elaborate script that has control logic or performs some kind of decision-making. One example of a Script Task could be querying Active Directory for some bit of information or talking to another data repository for data — both before running a package.
The Script Component is a lot more tightly married to the way the Data Flow works. Instead of running once during an entire package, a Script Component's main processing runs once for each row of the data being processed. Script Components also have three basic contexts they work in: data source, a data transformation, or a data destination. The Component is also a little less interactive — it doesn't support the kind of debugging that's in the Script Task, for one. Most of what you'd want to use the Script Component for would be things like row-by-row transformations, building a custom ODBC destination, or on-the-fly error handling or transformation actions that aren't handled through native SSIS functions.
The Advanced Approach: Programming with Custom Objects
As powerful as scripting can be in SSIS packages, sometimes it's simply not enough to get the job done. In some cases you may need to write (or have someone else write) a custom SSIS extension from scratch. This is not something you can do trivially; it requires an understanding of programming from the ground up. But with custom objects it's possible to use SSIS in a way that's far beyond simple automation of tasks.
For instance, if you have a data source that's simply not supported through any existing SSIS transform (i.e., some oddball proprietary data source that's no longer supported by the manufacturer), you could write a custom connection manager object to allow using that data as a native data source. Likewise, you can create custom tasks, log providers, or data flow components with the same programming libraries that are exposed through SSIS.
Each one of the above types of items is available as a base class, attribute and method set in the languages supported by SSIS: Visual Basic, C#, C++, J# and Jscript. C++, C# and VB tend to produce the best results since they tend to be broadly supported by both developers and vendors in these contexts. The idea is that the language you use shouldn't be a hindrance; they all plug into the same exposed programmatic interfaces. You can also create user interfaces for custom objects through standard Windows forms, whenever they're needed.
One extremely powerful example of the sort of thing that can be created through SSIS custom objects is a customized foreach enumerator. Let's say you want to create a set of programming classes to perform tasks for each set of objects in a collection, such as a set of tables in a database. If you want to implement such actions in a broad variety of contexts without reinventing the wheel each time, this is one of the best ways to do it. It's especially useful if you've created a custom connection manager to some new type of data (as per the above example) and want to create custom foreach actions to go with it.
Conclusion
The path you take to extending SSIS, whether it is scripting or programming, is always going to be dictated by both your needs and your abilities. Because you can use both approaches — even at the same time! — you don't have to devote yourself any more than is needed for the task at hand. You can mix and match as required, too.
For further examples you can of course look at the links above (which go to Microsoft's own documentation of SSIS package extensions), although they're a little light on specific implementations and examples. There's also some blogs that cover the subject: Peter DeBetta's SQL Programming Blog, for instance. I can also recommend the SSIS Junkie blog, which has a good deal of ongoing discussion of how to accomplish things in SSIS (both programmatically, and otherwise!).
ABOUT THE AUTHOR:
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2006 TechTarget
More from SearchSQLServer.com
SQL Server Integration Services programming basics
SQL Server Integration Services: Simplify database maintenance
Migrating SQL 2000 DTS packages to SSIS
