]]> _
_
_
Public Class ScriptMain
Inherits UserComponent
Private Const ColumnSeparator As String = "," + vbLf
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim colsCount As Integer = m_inputIdx.Length
Call Me.OutputBuffer.AddRow()
For colIndex As Integer = 0 To colsCount - 1
Dim inIndex As Integer = m_inputIdx(colIndex)
Dim outIndex As Integer = m_outputIdx(colIndex)
If Row.Buffer.IsNull(inIndex) Then
Continue For
End If
' Get input value.
Dim inValue As Object = Row.Buffer(inIndex)
If TypeOf inValue Is BlobColumn Then
' Translate into more useful object.
Dim blob As BlobColumn = CType(inValue, BlobColumn)
Select Case blob.ColumnInfo.DataType
Case DataType.DT_TEXT, DataType.DT_NTEXT
inValue = Row.Buffer.GetString(inIndex)
Case Else
inValue = blob.GetBlobData(0, CInt(blob.Length))
End Select
End If
' Set output value.
Dim outValue As Object = inValue
Select Case m_outputType(colIndex)
Case DataType.DT_DATE, DataType.DT_FILETIME, DataType.DT_DBDATE, DataType.DT_DBTIMESTAMP
outValue = Convert.ToDateTime(outValue)
Case DataType.DT_GUID
outValue = New Guid(outValue.ToString())
Case DataType.DT_STR, DataType.DT_WSTR, DataType.DT_TEXT, DataType.DT_NTEXT
If TypeOf outValue Is Byte() Then
' Encode as base64 value.
outValue = Convert.ToBase64String(CType(outValue, Byte()))
Else
outValue = outValue.ToString()
End If
Case DataType.DT_IMAGE
If TypeOf outValue Is String Then
' Assume input is base64 value.
outValue = Convert.FromBase64String(CStr(outValue))
End If
End Select
Me.OutputBuffer.Buffer(outIndex) = outValue
Next
End Sub ' Input_ProcessInputRow
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PreExecute()
Call MyBase.PreExecute()
Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0)
Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0)
' Setup dynamic input columns indexes.
Dim colsCount As Integer = input.InputColumnCollection.Count
m_inputIdx = New Integer(colsCount - 1) {}
m_outputIdx = New Integer(colsCount - 1) {}
m_outputType = New DataType(colsCount - 1) {}
For colIndex As Integer = 0 To colsCount - 1
Dim inColumn As IDTSInputColumn90 = input.InputColumnCollection(colIndex)
m_inputIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
inColumn.LineageID)
Dim outColumn As IDTSOutputColumn90 = output.OutputColumnCollection(inColumn.Name)
Try
m_outputIdx(colIndex) = -1
m_outputIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
output.Buffer, _
outColumn.LineageID)
Catch ex As Exception
' Under SQL 2008 if column is not used in the pipeline,
' it doesn't exist in the buffer.
End Try
m_outputType(colIndex) = outColumn.DataType
Next
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)
Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0)
If input.InputColumnCollection.Count = 0 OrElse Me.Columns <> m_prevCols Then
Dim selectedCols() As String = CStr(Me.Columns).Split( _
New String() {ColumnSeparator}, _
StringSplitOptions.RemoveEmptyEntries)
' Remove output columns, which are no longer in use.
Dim colsCount As Integer = output.OutputColumnCollection.Count
For colIndex As Integer = colsCount - 1 To 0 Step -1
Dim outColumn As IDTSOutputColumn90 = output.OutputColumnCollection(colIndex)
If Not IsFound_(selectedCols, outColumn.Name) Then
Call output.OutputColumnCollection.RemoveObjectByID(outColumn.ID)
End If
Next
' Setup input columns based on selection.
Call input.InputColumnCollection.RemoveAll()
Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput()
For Each col As String In selectedCols
Dim virtColumn As IDTSVirtualInputColumn90 = virtInput.VirtualInputColumnCollection(col)
Call virtInput.SetUsageType(virtColumn.LineageID, DTSUsageType.UT_READONLY)
Dim outColumn As IDTSOutputColumn90
Try
outColumn = output.OutputColumnCollection(col)
Catch ex As Exception
' New column.
outColumn = output.OutputColumnCollection.[New]()
outColumn.Name = col
End Try
Next
End If
Dim codePage As Integer = 0
If m_culture <> 0 Then
Dim ci As CultureInfo = New CultureInfo(m_culture)
codePage = ci.TextInfo.ANSICodePage
End If
' Update output columns with specified type.
For Each outColumn As IDTSOutputColumn90 In output.OutputColumnCollection
Call outColumn.SetDataTypeProperties( _
Me.Type, _
Me.Length, _
Me.Precision, _
Me.Scale, _
codePage)
Next
result = True
Catch ex As Exception
result = False
errMessage = ex.ToString()
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("Columns")
Call result.Add("Type")
' Setup dynamic.
Select Case Me.Type
Case DataType.DT_DECIMAL
Call result.Add("Scale")
Case DataType.DT_STR
Call result.Add("Length")
Call result.Add("CodePage")
Case DataType.DT_WSTR
Call result.Add("Length")
Case DataType.DT_NUMERIC
Call result.Add("Precision")
Call result.Add("Scale")
Case DataType.DT_TEXT
Call result.Add("CodePage")
End Select
Return CType(result.ToArray(GetType(String)), String())
End Function ' GetProperties
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function GetCultures() As Object()
Dim result As New ArrayList()
Dim cultures() As CultureInfo = CultureInfo.GetCultures(CultureTypes.AllCultures)
For Each ci As CultureInfo In cultures
Call result.Add(ci.DisplayName)
Next
GetCultures = result.ToArray()
End Function ' GetCultures
#Region "Properties"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property Columns() As String
Get
Columns = 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
If String.IsNullOrEmpty(m_prevCols) Then
m_prevCols = value
End If
m_inputCols = value
End Set
End Property ' Columns
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property Type() As DataType
Get
Type = m_type
End Get
Set(ByVal value As DataType)
If m_type <> value Then
m_type = value
m_length = 0
m_precision = 0
m_scale = 0
m_culture = 0
End If
End Set
End Property ' Type
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property Length() As Integer
Get
Length = m_length
End Get
Set(ByVal value As Integer)
m_length = value
End Set
End Property ' Length
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property Precision() As Integer
Get
Precision = m_precision
End Get
Set(ByVal value As Integer)
m_precision = value
End Set
End Property ' Precision
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property Scale() As Integer
Get
Scale = m_scale
End Get
Set(ByVal value As Integer)
m_scale = value
End Set
End Property ' Scale
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property CodePage() As String
Get
Dim result As String = String.Empty
If m_culture <> 0 Then
result = New CultureInfo(m_culture).DisplayName
End If
CodePage = result
End Get
Set(ByVal value As String)
m_culture = FindCultureByDisplay_(value)
End Set
End Property ' CodePage
#End Region ' Properties
#Region "Internals"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function IsFound_(ByVal list() As String, ByVal search As String) As Boolean
Dim result As Boolean = False
For Each item As String In list
If item.Equals(search, StringComparison.OrdinalIgnoreCase) Then
' Found.
result = True
Exit For
End If
Next
IsFound_ = result
End Function ' IsFound_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function FindCultureByDisplay_(ByVal display As String) As Integer
Dim result As Integer = 0
Dim cultures() As CultureInfo = CultureInfo.GetCultures(CultureTypes.AllCultures)
For Each ci As CultureInfo In cultures
If ci.DisplayName = display Then
' Found.
result = ci.LCID
Exit For
End If
Next
FindCultureByDisplay_ = result
End Function ' FindCultureByDisplay_
#End Region ' Internals
#Region "Attributes"
Private m_inputCols As String = String.Empty
Private m_prevCols As String = String.Empty
Private m_type As DataType = DataType.DT_BYREF_I4
Private m_length As Integer = 50
Private m_precision As Integer
Private m_scale As Integer
Private m_culture As Integer
Private m_inputIdx() As Integer
Private m_outputIdx() As Integer
Private m_outputType() As DataType
#End Region ' Attributes
End Class ' ScriptMain
]]>ScriptComponent_15af3c8df42f4b1f99e630053b223fe4trueCozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea