]]> _
_
_
Public Class ScriptMain
Inherits UserComponent
Private Const ColumnSeparator As String = "," + vbLf
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Try
Dim colsCount As Integer = m_inputIdx.Length
For colIndex As Integer = 0 To colsCount - 1
Dim bufIndex As Integer = m_inputIdx(colIndex)
Dim dt As DataType = m_inputTypes(colIndex)
' Get input value.
Dim inputValue As String = Nothing
If Not Row.Buffer.IsNull(bufIndex) Then
Select Case dt
Case DataType.DT_TEXT, DataType.DT_NTEXT
inputValue = Row.Buffer.GetString(bufIndex).Trim()
Case Else
inputValue = Row.Buffer(bufIndex).ToString().Trim()
End Select
End If
Dim outputValue As String = inputValue
Select Case Me.Action
Case ActionTypes.IsNull
If inputValue Is Nothing Then
' NULL value. Replace with value.
outputValue = m_replaceValue
End If
Case ActionTypes.SetNull
If Not inputValue Is Nothing AndAlso _
m_matchValue.Contains(inputValue) Then
' Value match. Replace with NULL.
outputValue = Nothing
End If
End Select
If outputValue = inputValue Then
' No need to replace.
Continue For
End If
' Replace value.
If outputValue Is Nothing Then
Call Row.Buffer.SetNull(bufIndex)
Else
Select Case dt
Case DataType.DT_TEXT, DataType.DT_NTEXT
Call Row.Buffer.SetString(bufIndex, outputValue)
Case DataType.DT_DATE
Row.Buffer(bufIndex) = Convert.ToDateTime(outputValue)
Case DataType.DT_GUID
Row.Buffer(bufIndex) = New Guid(outputValue)
Case Else
Row.Buffer(bufIndex) = outputValue
End Select
End If
Next
Catch ex As Exception
Call FireError_(ex.Message)
End Try
End Sub ' Input_ProcessInputRow
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PreExecute()
Call MyBase.PreExecute()
Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0)
' Setup dynamic input columns indexes.
Dim colsCount As Integer = input.InputColumnCollection.Count
m_inputIdx = New Integer(colsCount - 1) {}
m_inputTypes = New DataType(colsCount - 1) {}
For colIndex As Integer = 0 To colsCount - 1
Dim inputCol As IDTSInputColumn90 = input.InputColumnCollection(colIndex)
m_inputIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
inputCol.LineageID)
m_inputTypes(colIndex) = inputCol.DataType
Next
Dim actionValue As String = m_value
If m_isValueVar Then
' Action value is contained in variable.
actionValue = GetVariable_(actionValue).ToString()
End If
If Me.Action = ActionTypes.IsNull Then
' Setup replace value.
m_replaceValue = Regex.Unescape(actionValue)
Else
' Setup match value.
m_matchValue = New Hashtable()
Dim valueList() As String = CStr(actionValue).Split( _
New String() {vbLf}, _
StringSplitOptions.None)
For Each matchValue As String In valueList
If Not String.IsNullOrEmpty(matchValue) Then
matchValue = Regex.Unescape(matchValue)
End If
m_matchValue(matchValue) = Nothing
Next
End If
End Sub ' PreExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function Validate(ByRef errMessage As String) As Boolean
Dim result As Boolean
Try
Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0)
' Setup input columns based on selection.
Dim selectedCols() As String = CStr(Me.InputColumns).Split( _
New String() {ColumnSeparator}, _
StringSplitOptions.RemoveEmptyEntries)
Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput()
Call input.InputColumnCollection.RemoveAll()
For Each col As String In selectedCols
Dim virtColumn As IDTSVirtualInputColumn90 = virtInput.VirtualInputColumnCollection(col)
Call virtInput.SetUsageType(virtColumn.LineageID, DTSUsageType.UT_READWRITE)
Next
result = True
Catch ex As Exception
result = False
errMessage = ex.Message
End Try
Validate = result
End Function 'Validate
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of current properties based on current state.
Public Function GetProperties() As String()
Dim result As New ArrayList
' Setup static.
Call result.Add("InputColumns")
Call result.Add("Action")
' Setup dynamic.
Select Case Me.Action
Case ActionTypes.IsNull
Call result.Add("IsReplaceValueVariable")
Call result.Add(IIf(Me.IsReplaceValueVariable, "ReplaceValueVariable", "ReplaceValue"))
Case ActionTypes.SetNull
Call result.Add("IsMatchValueVariable")
Call result.Add(IIf(Me.IsMatchValueVariable, "MatchValueVariable", "MatchValue"))
End Select
Return CType(result.ToArray(GetType(String)), String())
End Function ' GetProperties
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub OnInputPathAttached(ByVal inputID As Integer)
Call MyBase.OnInputPathAttached(inputID)
' Initialize with all string columns selected.
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput()
For Each column As IDTSVirtualInputColumn90 In virtInput.VirtualInputColumnCollection
If column.DataType <> DataType.DT_STR AndAlso _
column.DataType <> DataType.DT_WSTR AndAlso _
column.DataType <> DataType.DT_TEXT AndAlso _
column.DataType <> DataType.DT_NTEXT Then
Continue For
End If
' String column.
Call virtInput.SetUsageType(column.LineageID, DTSUsageType.UT_READWRITE)
Next
End Sub ' OnInputPathAttached
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub OnInputPathDetached(ByVal inputID As Integer)
MyBase.OnInputPathDetached(inputID)
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
Call input.InputColumnCollection.RemoveAll()
End Sub
#Region "Properties"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property InputColumns() As String
Get
InputColumns = m_inputCols
End Get
Set(ByVal value As String)
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
If input.IsAttached Then
If String.IsNullOrEmpty(value) Then
' Setup based on input columns collection.
value = GetInputCols_()
End If
Else
' Reset when no input is attached.
value = String.Empty
End If
m_inputCols = value
End Set
End Property ' InputColumns
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property Action() As ActionTypes
Get
Action = m_action
End Get
Set(ByVal value As ActionTypes)
If m_action <> value Then
m_action = value
m_value = String.Empty
End If
End Set
End Property ' Action
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property IsReplaceValueVariable() As Boolean
Get
IsReplaceValueVariable = m_isValueVar
End Get
Set(ByVal value As Boolean)
If m_isValueVar <> value Then
m_isValueVar = value
m_value = String.Empty
End If
End Set
End Property ' IsReplaceValueVariable
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
Public Property ReplaceValue() As String
Get
ReplaceValue = m_value
End Get
Set(ByVal value As String)
m_value = value
End Set
End Property ' ReplaceValue
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property ReplaceValueVariable() As String
Get
ReplaceValueVariable = m_value
End Get
Set(ByVal value As String)
m_value = value
End Set
End Property ' ReplaceValueVariable
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property IsMatchValueVariable() As Boolean
Get
IsMatchValueVariable = m_isValueVar
End Get
Set(ByVal value As Boolean)
If m_isValueVar <> value Then
m_isValueVar = value
m_value = String.Empty
End If
End Set
End Property ' IsMatchValueVariable
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property MatchValue() As String
Get
MatchValue = m_value
End Get
Set(ByVal value As String)
m_value = value
End Set
End Property ' MatchValue
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property MatchValueVariable() As String
Get
MatchValueVariable = m_value
End Get
Set(ByVal value As String)
m_value = value
End Set
End Property ' MatchValueVariable
#End Region ' Properties
#Region "Internals"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub FireError_(ByVal message As String)
Dim cancel As Boolean
Call Me.ComponentMetaData.FireError( _
0, _
"Null Transformation", _
message, _
String.Empty, _
0, _
cancel)
End Sub ' FireError_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function GetVariable_(ByVal varName As String) As Object
Dim result As Object
Dim vars As IDTSVariables90 = Nothing
Call Me.VariableDispenser.LockOneForRead(varName, vars)
Try
result = vars(varName).Value
Finally
Call vars.Unlock()
End Try
GetVariable_ = result
End Function ' GetVariable_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function GetInputCols_() As String
Dim result As String = String.Empty
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
For Each column As IDTSInputColumn90 In input.InputColumnCollection
If Not String.IsNullOrEmpty(result) Then
result += ColumnSeparator
End If
result += column.Name
Next
GetInputCols_ = result
End Function ' GetInputCols_
#End Region ' Internals
#Region "Attributes"
Public Enum ActionTypes
IsNull
SetNull
End Enum ' ActionTypes
Private m_inputCols As String = String.Empty
Private m_action As ActionTypes = ActionTypes.IsNull
Private m_isValueVar As Boolean
Private m_value As String
Private m_inputIdx() As Integer
Private m_inputTypes() As DataType
Private m_replaceValue As String
Private m_matchValue As Hashtable
#End Region ' Attributes
End Class ' ScriptMain
]]>ScriptComponent_e52963cf4d394e2c80bfd3c9df07cdd7trueCozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea