ETL in Integration Services (SSIS) - Closing the gaps

In the software industry it is quite common to find large, feature rich frameworks that do a very good job at getting us as developers 90% there yet still leave us with gaps in our solution.

This is not to say that we are ungrateful for the 90% but let's face it the 10% is usually the hardest part.

Like any good framework, SQL Server Integration Services is a rich platform that provides most of what you need in your ETL solutions but that last piece can be a nightmare.

Our aim is to help close that gap by developing tools and components for use in your ETL solution, that said, we'd love to share a bit of the nuts and bolts of what takes to build a custom SSIS component.

Getting Started

As is often the case Microsoft has given developers the ability to close the gap in SSIS by creating custom components. The full scope of customization in SSIS is beyond the scope of this article but I will be digging deeper into Custom Connection Managers and Custom Sources as this series progresses. For now, I think it is worth taking the time to introduce custom components as they are a rich subject and show you an easy sample to get started.

Plug in Overview

Microsoft has made it pretty simple to incorporate your own code into Integration Services.
In general, you will be writing code that participates in what is known as the Pipeline of SSIS.
The Pipeline is similar to a stream conceptually speaking in that it allows you to add code inline through scripting or custom components so that you can act on the buffers that SSIS is managing. Here is a quick overview of a VERY simple transform that might help you get started. With a few project references in a class library project and a class that derives from a Pipeline base class you can be up and running.

References

Microsoft.SqlServer.PipelineHost Microsoft.SqlServer.DTSPipelineWrap

You will also need a using statement for:

using Microsoft.SqlServer.Dts.Pipeline;
<br /><br />
Derive your class from PipelineComponent such as:  
<br /> <br />

public class MyComponent : PipelineComponent


Add the attribute to your class:

[DtsPipelineComponent(DisplayName = "MyComponent",

                         ComponentType = ComponentType.Transform)

   ]

Note: DtsPipelineComponent has other properties to specify description, icon, editor, etc

Now you have to override ProcessInput in your class:

public class MyComponent : PipelineComponent

{

   public override void ProcessInput(int inputID, PipelineBuffer buff)

   {

       int currentRows = buff.RowCount;

   }

}

That's it! This is just a simple transform but I think you can see how Microsoft has made it easy to extend SSIS allowing developers to focus on the problem domain. Keep in mind that Source and Target components will vary just a little in implementation but the purpose of this article was to demonstrate just how easy it is to get started and plug in your custom component to SSIS.