]]> _
_
_
Public Class ScriptMain
Inherits UserComponent
Private Const ColumnSeparator As String = "," + vbLf
Private Const Salt As String = "7ADC83D055D6459c8C1793C8D41A36FE"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim colsCount As Integer = m_inputIdx.Length
For colIndex As Integer = 0 To colsCount - 1
Dim bufIndex As Integer = m_inputIdx(colIndex)
If Row.Buffer.IsNull(bufIndex) Then
Continue For
End If
Dim blob As BlobColumn = CType(Row.Buffer(bufIndex), BlobColumn)
Dim data() As Byte = blob.GetBlobData(0, CInt(blob.Length))
Select Case Me.EncryptionType
Case EncryptionTypes.AES, EncryptionTypes.TripleDES, _
EncryptionTypes.DES, EncryptionTypes.RC2
' Setup crypto stream.
Dim cs As CryptoStream
Dim ms As New MemoryStream
Dim sAlg As SymmetricAlgorithm = CType(m_encryptor, SymmetricAlgorithm)
If Me.Action = ActionTypes.Encryption Then
cs = New CryptoStream(ms, sAlg.CreateEncryptor(), CryptoStreamMode.Write)
Else
cs = New CryptoStream(ms, sAlg.CreateDecryptor(), CryptoStreamMode.Read)
End If
Try
Call cs.Write(data, 0, data.Length)
Finally
Call cs.Dispose()
End Try
data = ms.ToArray()
Case EncryptionTypes.RSA
Dim rsaCer As X509Certificate2 = CType(m_encryptor, X509Certificate2)
Dim rsaCsp As RSACryptoServiceProvider
If Me.Action = ActionTypes.Encryption Then
rsaCsp = CType(rsaCer.PublicKey.Key, RSACryptoServiceProvider)
data = rsaCsp.Encrypt(data, True)
Else
rsaCsp = CType(rsaCer.PrivateKey, RSACryptoServiceProvider)
data = rsaCsp.Decrypt(data, True)
End If
' RSACryptoServiceProvider reverses the order of encrypted bytes.
' Reverse back to make the data compatible with CAPI.
Call Array.Reverse(data)
End Select
' Set new column data.
Call blob.ResetBlobData()
Call blob.AddBlobData(data)
Next
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) {}
For colIndex As Integer = 0 To colsCount - 1
m_inputIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
input.InputColumnCollection(colIndex).LineageID)
Next
m_encryptor = CreateEncryptor_()
End Sub ' PreExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function Validate(ByRef errMessage As String) As Boolean
Dim result As Boolean
Try
If Me.EncryptionType <> EncryptionTypes.RSA AndAlso _
String.IsNullOrEmpty(Me.Password) Then
Throw New Exception("Specify password.")
End If
If Me.EncryptionType = EncryptionTypes.RSA AndAlso _
String.IsNullOrEmpty(Me.CertificateFile) Then
Throw New Exception("Specify certificate file.")
End If
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.None)
Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput()
Call input.InputColumnCollection.RemoveAll()
For Each col As String In selectedCols
If Not String.IsNullOrEmpty(col) Then
Dim virtColumn As IDTSVirtualInputColumn90 = virtInput.VirtualInputColumnCollection(col)
Call virtInput.SetUsageType(virtColumn.LineageID, DTSUsageType.UT_READWRITE)
End If
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")
Call result.Add("EncryptionType")
Call result.Add("IsPasswordVariable")
' Setup dynamic.
Call result.Add(IIf(Me.IsPasswordVariable, "PasswordVariable", "Password"))
If Me.EncryptionType = EncryptionTypes.RSA Then
Call result.Add("IsCertificateFileVariable")
Call result.Add(IIf( _
Me.IsCertificateFileVariable, _
"CertificateFileVariable", _
"CertificateFile"))
End If
Return CType(result.ToArray(GetType(String)), String())
End Function ' GetProperties
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns only columns with type DT_IMAGE.
Public Function GetInputColumns() As Object()
Dim result As New ArrayList
Dim virtInput As IDTSVirtualInput90 = MyBase.ComponentMetaData.InputCollection(0).GetVirtualInput()
For Each virtColumn As IDTSVirtualInputColumn90 In virtInput.VirtualInputColumnCollection
If virtColumn.DataType = DataType.DT_IMAGE Then
Call result.Add(virtColumn.Name)
End If
Next
Call result.Sort()
GetInputColumns = result.ToArray()
End Function ' GetInputColumns
#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 Not input.IsAttached Then
' 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)
m_action = value
End Set
End Property ' Action
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property EncryptionType() As EncryptionTypes
Get
EncryptionType = m_type
End Get
Set(ByVal value As EncryptionTypes)
m_type = value
End Set
End Property ' EncryptionType
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property IsPasswordVariable() As Boolean
Get
IsPasswordVariable = m_isPasswordVar
End Get
Set(ByVal value As Boolean)
If m_isPasswordVar <> value Then
m_isPasswordVar = value
m_password = String.Empty
End If
End Set
End Property ' IsPasswordVariable
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property Password() As String
Get
Password = m_password
End Get
Set(ByVal value As String)
m_password = value
End Set
End Property ' Password
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property PasswordVariable() As String
Get
PasswordVariable = m_password
End Get
Set(ByVal value As String)
m_password = value
End Set
End Property ' PasswordVariable
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property IsCertificateFileVariable() As Boolean
Get
IsCertificateFileVariable = m_isCertificateVar
End Get
Set(ByVal value As Boolean)
If m_isCertificateVar <> value Then
m_isCertificateVar = value
m_certificate = String.Empty
End If
End Set
End Property ' IsCertificateFileVariable
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property CertificateFile() As String
Get
CertificateFile = m_certificate
End Get
Set(ByVal value As String)
m_certificate = value
End Set
End Property ' CertificateFile
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property CertificateFileVariable() As String
Get
CertificateFileVariable = m_certificate
End Get
Set(ByVal value As String)
m_certificate = value
End Set
End Property ' CertificateFileVariable
#End Region ' Properties
#Region "Internals"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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 CreateEncryptor_() As Object
Dim result As Object
' Get password.
Dim password As String = Me.Password
If Me.IsPasswordVariable AndAlso _
Not String.IsNullOrEmpty(Me.PasswordVariable) Then
password = GetVariable_(Me.PasswordVariable).ToString()
End If
If password = "" Then
password = Nothing
End If
' Setup key derivation from password.
Dim der As New Rfc2898DeriveBytes( _
Me.Password, _
Encoding.ASCII.GetBytes(Salt))
Select Case Me.EncryptionType
Case EncryptionTypes.AES
Dim aes As Rijndael = Rijndael.Create()
aes.Key = der.GetBytes(32)
aes.IV = der.GetBytes(16)
result = aes
Case EncryptionTypes.RSA
' Get certificate file.
Dim cerFile As String = Me.CertificateFile
If Me.IsCertificateFileVariable AndAlso _
Not String.IsNullOrEmpty(Me.CertificateFileVariable) Then
cerFile = GetVariable_(Me.CertificateFileVariable).ToString()
End If
Dim cer As X509Certificate2 = New X509Certificate2(cerFile, password)
If Me.Action = ActionTypes.Decryption AndAlso cer.PrivateKey Is Nothing Then
Throw New Exception("Private key not found.")
End If
result = cer
Case EncryptionTypes.TripleDES
Dim tripleDes As TripleDES = TripleDESCryptoServiceProvider.Create()
tripleDes.Key = der.GetBytes(24)
tripleDes.IV = der.GetBytes(8)
result = tripleDes
Case EncryptionTypes.DES
Dim des As DES = des.Create()
des.Key = der.GetBytes(8)
des.IV = der.GetBytes(8)
result = des
Case EncryptionTypes.RC2
Dim rc2 As RC2 = rc2.Create()
rc2.Key = der.GetBytes(16)
rc2.IV = der.GetBytes(8)
result = rc2
Case Else
Throw New Exception("Unhandled encryption type.")
End Select
CreateEncryptor_ = result
End Function ' CreateEncryptor_
#End Region ' Internals
#Region "Attributes"
Public Enum ActionTypes
Encryption
Decryption
End Enum ' ActionTypes
Public Enum EncryptionTypes
AES
RSA
TripleDES
DES
RC2
End Enum ' EncryptionTypes
Private m_inputCols As String = String.Empty
Private m_action As ActionTypes = ActionTypes.Encryption
Private m_type As EncryptionTypes = EncryptionTypes.AES
Private m_isPasswordVar As Boolean
Private m_password As String
Private m_isCertificateVar As Boolean
Private m_certificate As String
Private m_inputIdx() As Integer
Private m_encryptor As Object
#End Region ' Attributes
End Class ' ScriptMain
]]>ScriptComponent_fbafc79217dc4580a2e147cada70a786trueCozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea