Thursday, May 8, 2008

SSIS Custom Component Custom Property

When building an Integration Services custom component, you inherit from the class Microsoft.SqlServer.Dts.Pipeline.PipelineComponent. You can override any of the methods within this class to add your own functionality. If you want to add a custom property to the component, you will need to override the ProvideComponentProperties subroutine.

For example, if you want to add a custom property that will act as a switch for removing or including empty strings, you would use code similar to the following:

Dim RemoveEmptyStringsProperty As IDTSCustomProperty90
RemoveEmptyStringsProperty = ComponentMetaData.CustomPropertyCollection.[New]
RemoveEmptyStringsProperty.Name = "RemoveEmptyStrings"
RemoveEmptyStringsProperty.Description = "Boolean that determines if empty strings should be removed. Value should be True or False."
RemoveEmptyStringsProperty.Value = "False" 'Default value

Then in your execution logic (in the ProcessInput subroutine), you would use code similar to:

If ComponentMetaData.CustomPropertyCollection("RemoveEmptyStrings").Value.ToString.ToUpper = "TRUE" Then
    'Perform logic
End If

This will allow the custom property to show up on the initial Edit screen of the custom component. When creating a package, the developer can select the option appropriate to their business logic, and that option will decide the course of action at runtime.

Version: SQL Server 2005 SP2, VB.NET 2.0


Anonymous said...

Pretty neat - BTW: Can you use input masks for custom property data? So, in this case, could you restrict the values to just true or false (possible drop down)?

Jessica M. Moss said...

Hi RM,

You can restrict the values for a property by creating a custom user interface for the component. You'll need to design a Windows Form and a class that inherits from the IDtsComponentUI interface. Then set the UITypeName value to point to your new UI. Check back in a few weeks, and I'll have written up a full post for you :)