Developing custom SSIS component – 9 lessons learned

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

One of the compelling features of Microsoft SQL Server Integration Services (SSIS) is its extensibility. Although SQL Server comes with a wide array of SSIS components (including different data sources, transformation tasks and logical flow control operations), sometimes there is a need to do something unique in your SSIS package, something that is not supported out of the box.
Luckily, this is possible and it’s not very hard to do. All that you need a Visual Studio and some knowledge of SSIS extensibility framework.
If you decide to develop your own SSIS component, then there is good documentation available at MSDN, there are a few great blog posts outlining the process from end to end, and there is source code on Codeplex. In my short blog post I’m not planning to duplicate all of above, I’m just trying outline a few “gotchas” which I went though myself while developing SSIS component.

1. Understand what you want to do. SSIS supports two distinct types of components: tasks and data flow tasks. “Task” is … well, a task. Some custom action which you wold like to execute in your control flow, like sending email when package execution fails (by the way, email task comes standard). “Data flow task” is dealing with data flow, i.e. extracting, transforming and loading data (ETL). The chances are that you’ll be more interested in the later, because I think the primary reason for developing custom SSIS component is a building a direct interface with some specific system which is not supported OOB (most likely by utilizing that system’s API). If it’s that you want, then you’ll need to implement your component logic in a class derived from Microsoft.SqlServer.Dts.Pipeline.PipelineComponent.
2. Implement custom property editor. Although technically all that you need for a custom data flow component is to subclass PipelineComponent, it’s more convenient for the user of your component to work with a specialized UI than with generic property interface.
3. Implement custom connection manager. There is substantial chance that SSIS package would contain a multiple instances of your pipeline component. In this case it’s much easier to specify connection to your custom data source once and then reuse it between pipeline components. Note that you’ll need to call ComponentMetaData.RuntimeConnectionCollection.New() method (preferably inside ProvideComponentProperties method) in order to let SSIS engine know that your component requires custom connection. Then, inside AcquireConnections() method you’ll need to validate if ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager is set and InnerObject is your custom connection manager.
4. Override PipelineComponent.Validate() method. This will give user a clear indication when some of the properties of your component are either not entered or invalid.
5. Return DTSValidationStatus.VS_NEEDSNEWMETADATA from Validate() method when your component properties have changed and you need to rebuild outputs accordingly. As a response SSIS runtime would call your component’s ReinitializeMetaData() method. And inside ReinitializeMetaData() you can rebuild (or create if none existed before) outputs.
6. Be aware that SSIS runtime and execution engines are .NET 4.0. Even if your component is written in 4.6.1, it still be executed under 4.0. Usually it’s not that important, however there are cases there differences between 4.0 and 4.6 (or 4.6) could be critical. For example, I run into situation where I needed to call external API which only supported TLS 1.2. .NET 4.0 by default doesn’t use TLS 1.2 (unless you specifically) request it. The code worked fine when I run it outside SSIS, but was giving errors under SSIS.
7. Be aware of incompatible versions of SQL Data Tools. Unfortunately, in your development you’ll have to target a specific version of SQL Data Tools. When you developing the component, you’ll have to reference components like Microsoft.SqlServer.Dts.Design.dll, Microsoft.SqlServer.DTSPipelineWrap.dll, Microsoft.SQLServer.ManagedDTS.dll, etc. These components are found in GAC and they are specific to SQL Data Tools version. And they neither forward no backward compatible. For example, SQL Server 2012 comes with SQL Data Tools which is based on Visual Studio 2010. If you also installed Visual Studio 2013 or 2016 then it would upgrade your version of SQL Data Tools (to the one which comes with SQL Server 2013 or 2016 correspondingly). And the problem is that if your component is referencing a different version of SSIS DLLs then it would not come up SQL Data Tools toolbox.
8. Remember to register your component in GAC and copy to SQL server folders. Pipeline components should be  copied to C:\Program Files (x86)\Microsoft SQL Server\<version>\DTS\PipelineComponents and custom connection managers to C:\Program Files (x86)\Microsoft SQL Server\<version>\DTS\Connections. If you have both pipeline component and connection manager in the same Dll, then you should copy it to both places.
9.  SQL Server 2012 requires pipeline component to have custom icon. Make sure to set  DtsPipelineComponentAttribute.IconResource. Other SQL Server versions don’t require that.