Regex Match Transformation

Library references

• System
• System.Data
• Microsoft.SqlServer.TxScript
• Microsoft.SqlServer.PipelineHost
• Microsoft.SqlServer.DTSPipelineWrap
• Microsoft.SqlServer.DTSRuntimeWrap
• Microsoft.SqlServer.ManagedDTS
• CozyRoc.SSISPlus

ScriptMain

' CozyRoc SQL Server Integration Services user script component ' ' Copyright (c) 2006-2009 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. Imports System Imports System.ComponentModel Imports System.Text.RegularExpressions Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() {"Column", "Pattern", "IgnoreCase", "Multiline"})> _ <ValidateProperties("Validate")> _ Public Class ScriptMain Inherits UserComponent ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Dim input As String = Row.Buffer(m_columnIndex).ToString() If m_regex.IsMatch(input) Then Call Row.DirectRowToMatch() Else Call Row.DirectRowToNoMatch() End If End Sub ' Input_ProcessInputRow ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub PreExecute() Call MyBase.PreExecute() ' Initialize regular expression object. Dim options As RegexOptions = RegexOptions.Compiled If Me.IgnoreCase Then options = options Or RegexOptions.IgnoreCase End If If Me.Multiline Then options = options Or RegexOptions.Multiline End If options = RegexOptions.Multiline m_regex = New Regex(Me.Pattern, options) ' Setup column index. Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) m_columnIndex = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ input.InputColumnCollection(0).LineageID) End Sub ' PreExecute ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Try If String.IsNullOrEmpty(Me.Column) Then Throw New ApplicationException("Select input column.") End If If String.IsNullOrEmpty(Me.Pattern) Then Throw New ApplicationException("Specify regular expression.") End If ' Setup input columns based on selection. Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput() Call input.InputColumnCollection.RemoveAll() Dim virtColumn As IDTSVirtualInputColumn90 = virtInput.VirtualInputColumnCollection( _ Me.Column) Call virtInput.SetUsageType(virtColumn.LineageID, DTSUsageType.UT_READONLY) result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Select input column to use in regular expression filter.")> _ <Category("Input")> _ <InputVirtualColumn()> _ Public Property Column() As String Get Column = m_column End Get Set(ByVal value As String) m_column = value End Set End Property ' Column ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Specify filter regular expression.")> _ <Category("Regex")> _ <RegularExpressionDialog()> _ Public Property Pattern() As String Get Pattern = m_pattern End Get Set(ByVal value As String) m_pattern = value End Set End Property ' Pattern ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Specifies case-insensitive matching.")> _ <Category("Regex")> _ Public Property IgnoreCase() As Boolean Get IgnoreCase = m_ignoreCase End Get Set(ByVal value As Boolean) m_ignoreCase = value End Set End Property ' IgnoreCase ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Description("Multiline mode. Changes the meaning of ^ and $ so they match at the beginning and end, respectively, of any line, and not just the beginning and end of the entire string.")> _ <Category("Regex")> _ Public Property Multiline() As Boolean Get Multiline = m_multiline End Get Set(ByVal value As Boolean) m_multiline = value End Set End Property ' Multiline #End Region ' Properties #Region "Attributes" Private m_column As String Private m_pattern As String Private m_ignoreCase As Boolean Private m_multiline As Boolean Private m_columnIndex As Integer Private m_regex As Regex #End Region ' Attributes End Class ' ScriptMain