_
_
_
_
_
Public Class ScriptMain
Inherits UserComponent
Public Const FetchTemplate_3P As String = "{1}{2}"
Public Const AttrTemplate_1P As String = ""
Public Const CondTemlate_2P As String = ""
Public Const ListDelimiter As String = "," + vbLf
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Dim valueList() As String
If m_cache Is Nothing Then
valueList = GetLookupRow_(Row)
Else
' Cache is enabled.
Dim cacheKey As String = GetCacheKey_(Row)
If m_cache.Contains(cacheKey) Then
valueList = CType(m_cache(cacheKey), String())
Else
valueList = GetLookupRow_(Row)
m_cache(cacheKey) = valueList
End If
End If
If Not valueList Is Nothing Then
' Lookup row found.
Dim colsCount As Integer = m_valueIdx.Length
For colIndex As Integer = 0 To colsCount - 1
Dim value As String = valueList(colIndex)
Dim valueIndex As Integer = m_valueIdx(colIndex)
If value Is Nothing Then
Call Row.Buffer.SetNull(valueIndex)
Else
Select Case m_valueType(colIndex)
Case DataType.DT_GUID
Row.Buffer(valueIndex) = New Guid(value)
Case DataType.DT_DBTIMESTAMP
Case DataType.DT_DATE
Case DataType.DT_FILETIME
Case DataType.DT_DBDATE
Row.Buffer(valueIndex) = Convert.ToDateTime(value)
Case DataType.DT_IMAGE
Row.Buffer(valueIndex) = Convert.FromBase64String(value)
Case Else
Row.Buffer(valueIndex) = value
End Select
End If
Next
End If
End Sub ' Input_ProcessInputRow
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PreExecute()
Call MyBase.PreExecute()
' Setup connection.
m_connection = CType( _
Me.Connections.CrmConnection.AcquireConnection(Nothing), _
ICrmConnection)
Call m_connection.Connect()
If Me.UseCache Then
m_cache = New Hashtable()
End If
Dim input As IDTSInput100 = MyBase.ComponentMetaData.InputCollection(0)
Dim colsCount As Integer = input.InputColumnCollection.Count
' Setup key index and condition list.
m_keyIdx = New Integer(colsCount - 1) {}
For colIndex As Integer = 0 To colsCount - 1
Dim inputCol As IDTSInputColumn100 = input.InputColumnCollection(colIndex)
' Set key index.
m_keyIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
inputCol.LineageID)
' Set condition list.
Dim extCol As IDTSExternalMetadataColumn100 = input.ExternalMetadataColumnCollection.GetObjectByID( _
inputCol.ExternalMetadataColumnID)
m_condTemplate_P += String.Format( _
CondTemlate_2P, _
extCol.Name, _
"{" + colIndex.ToString() + "}")
Next
Dim output As IDTSOutput100 = MyBase.ComponentMetaData.OutputCollection(0)
colsCount = output.OutputColumnCollection.Count
' Setup value index and attribute list.
m_attrToIdx = New Hashtable()
m_valueIdx = New Integer(colsCount - 1) {}
m_valueType = New DataType(colsCount - 1) {}
Dim attrList As String = String.Empty
For colIndex As Integer = 0 To colsCount - 1
Dim outputCol As IDTSOutputColumn100 = output.OutputColumnCollection(colIndex)
m_attrToIdx(outputCol.Name) = colIndex
' Set value index and type.
m_valueIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
outputCol.LineageID)
m_valueType(colIndex) = outputCol.DataType
' Set attribute list.
attrList += String.Format(AttrTemplate_1P, outputCol.Name)
Next
' Setup fetch template.
m_fetchTemplate_1P = String.Format( _
FetchTemplate_3P, _
Me.Entity, _
attrList, _
"{0}")
End Sub ' PreExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PostExecute()
Call MyBase.PostExecute()
Call m_connection.Close()
End Sub ' PostExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function Validate(ByRef errMessage As String) As Boolean
Dim result As Boolean
Try
If String.IsNullOrEmpty(Me.CrmConnection) Then
Throw New ApplicationException("Select Dynamics CRM connection.")
End If
If String.IsNullOrEmpty(Me.Entity) Then
Throw New ApplicationException("Select entity.")
End If
If String.IsNullOrEmpty(Me.KeyAttribute) Then
Throw New ApplicationException("Specify key attribute(s).")
End If
If String.IsNullOrEmpty(Me.ValueAttribute) Then
Throw New ApplicationException("Select value attribute.")
End If
Dim keyList() As String = Me.KeyAttribute.Split( _
New String() {ListDelimiter}, _
StringSplitOptions.None)
Dim input As IDTSInput100 = Me.ComponentMetaData.InputCollection(0)
If input.IsAttached AndAlso input.InputColumnCollection.Count < keyList.Length Then
Throw New Exception("Map input columns.")
End If
Dim output As IDTSOutput100 = MyBase.ComponentMetaData.OutputCollection(0)
If output.OutputColumnCollection.Count = 0 Then
' Setup collection output columns.
Call SetupOutputColumns_()
End If
' Store connection information in the runtime connection collection, too.
' Cannot directly use RuntimeConnectionCollection in the property get/set
' because of issues with multi-threading.
Me.ComponentMetaData.RuntimeConnectionCollection("CrmConnection").ConnectionManagerID = Me.CrmConnection
result = True
Catch ex As Exception
result = False
errMessage = ex.Message
End Try
Validate = result
End Function 'Validate
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub ReinitializeMetaData()
Call MyBase.ReinitializeMetaData()
If MyBase.ComponentMetaData.InputCollection.Count = 0 Then
' At least one input must exist.
Throw New Exception("No inputs.")
End If
If String.IsNullOrEmpty(Me.KeyAttribute) Then
Throw New Exception("Key attribute(s) not selected.")
End If
' Get entity attributes.
Dim attrList() As ICrmAttribute = GetAttributes_()
Dim input As IDTSInput100 = MyBase.ComponentMetaData.InputCollection(0)
' Cleanup.
input.ExternalMetadataColumnCollection.IsUsed = True
Call input.InputColumnCollection.RemoveAll()
Call input.ExternalMetadataColumnCollection.RemoveAll()
' Setup attribute metadata.
Dim keyList() As String = Me.KeyAttribute.Split( _
New String() {ListDelimiter}, _
StringSplitOptions.None)
For Each key As String In keyList
' Find CRM attribute.
Dim attr As ICrmAttribute = FindAttribute_(attrList, key)
' Setup external column.
Dim column As IDTSExternalMetadataColumn100 = input.ExternalMetadataColumnCollection.[New]()
column.Name = attr.Name
column.DataType = CType(attr.AttributeType, DataType)
column.Length = attr.Length
column.Precision = attr.Precision
column.Scale = attr.Scale
column.CodePage = 0
Next
End Sub ' ReinitializeMetaData
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of entities in the currently selected Dynamics CRM connection.
Public Function GetEntityList() As String()
Dim result() As String
Dim connection As ICrmConnection = CType( _
Me.DesignConnections(Me.CrmConnection).AcquireConnection(Nothing), _
ICrmConnection)
Call connection.Connect()
Try
result = connection.GetEntityList(2) ' 2 - Read
Call Array.Sort(result)
Finally
Call connection.Close()
End Try
GetEntityList = result
End Function ' GetEntityList
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of attributes in the currently selected entity.
Public Function GetAttributeList() As String()
Dim result As ArrayList = New ArrayList()
Dim attrList() As ICrmAttribute = GetAttributes_()
For Each attr As ICrmAttribute In attrList
Call result.Add(attr.Name)
Next
Call result.Sort()
GetAttributeList = CType(result.ToArray(GetType(String)), String())
End Function ' GetAttributeList
#Region "Properties"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property CrmConnection() As String
Get
CrmConnection = m_crmConnection
End Get
Set(ByVal value As String)
m_crmConnection = value
End Set
End Property ' CrmConnection
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property Entity() As String
Get
Entity = m_entity
End Get
Set(ByVal value As String)
If m_entity <> value Then
m_entity = value
m_attributes = Nothing
Me.KeyAttribute = String.Empty
Me.ValueAttribute = String.Empty
End If
End Set
End Property ' Entity
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property KeyAttribute() As String
Get
KeyAttribute = m_keyAttribute
End Get
Set(ByVal value As String)
If m_keyAttribute <> value Then
If Not String.IsNullOrEmpty(m_keyAttribute) Then
' Force external columns to be reloaded.
If Me.ComponentMetaData.InputCollection.Count > 0 Then
Dim input As IDTSInput100 = Me.ComponentMetaData.InputCollection(0)
Call input.ExternalMetadataColumnCollection.RemoveAll()
End If
End If
m_keyAttribute = value
End If
End Set
End Property ' KeyAttribute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property ValueAttribute() As String
Get
ValueAttribute = m_valueAttribute
End Get
Set(ByVal value As String)
If m_valueAttribute <> value Then
If Not String.IsNullOrEmpty(m_valueAttribute) Then
' Force output columns to be reloaded.
If Me.ComponentMetaData.OutputCollection.Count > 0 Then
Dim output As IDTSOutput100 = Me.ComponentMetaData.OutputCollection(0)
Call output.OutputColumnCollection.RemoveAll()
End If
End If
m_valueAttribute = value
End If
End Set
End Property ' ValueAttribute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property UseCache() As Boolean
Get
UseCache = m_useCache
End Get
Set(ByVal value As Boolean)
m_useCache = value
End Set
End Property ' UseCache
#End Region ' Properties
#Region "Internals"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private ReadOnly Property CrmConnectionType() As String()
Get
CrmConnectionType = New String() {"DYNAMICS-CRM"}
End Get
End Property ' CrmConnectionType
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub FireError_(ByVal message As String)
Dim cancel As Boolean = False
Call MyBase.ComponentMetaData.FireError( _
0, _
"Dynamics CRM Lookup", _
message, _
String.Empty, _
0, _
cancel)
End Sub ' FireError_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function FindAttribute_( _
ByVal attrList() As ICrmAttribute, _
ByVal name As String) As ICrmAttribute
Dim result As ICrmAttribute = Nothing
For Each attr As ICrmAttribute In attrList
If attr.Name.Equals(name, StringComparison.OrdinalIgnoreCase) Then
' Found.
result = attr
Exit For
End If
Next
If result Is Nothing Then
Throw New Exception(String.Format("'{0}' attribute not found.", name))
End If
FindAttribute_ = result
End Function ' FindAttribute_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Setups cache key based on specified key attributes.
Private Function GetCacheKey_(ByVal row As InputBuffer) As String
Dim result As String = String.Empty
For Each idx As Integer In m_keyIdx
Dim key As String = String.Empty
If Not row.Buffer.IsNull(idx) Then
key = row.Buffer(idx).ToString() + "_"
End If
result += key
Next
GetCacheKey_ = result
End Function ' GetCacheKey_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Uses CRM Fetch to retrieve required lookup row.
Private Function GetLookupRow_(ByVal row As InputBuffer) As String()
Dim result() As String = Nothing
' Setup fetch conditions.
Dim colsCount As Integer = m_keyIdx.Length
Dim condList() As Object = New Object(colsCount - 1) {}
For colIndex As Integer = 0 To colsCount - 1
Dim keyIndex As Integer = m_keyIdx(colIndex)
Dim key As String = String.Empty
If Not row.Buffer.IsNull(keyIndex) Then
key = row.Buffer(keyIndex).ToString()
End If
condList(colIndex) = SecurityElement.Escape(key)
Next
' Fetch data.
Dim fetchStmt As String = String.Format( _
m_fetchTemplate_1P, _
String.Format(m_condTemplate_P, condList))
Dim doc As XmlDocument = New XmlDocument()
Try
Call doc.LoadXml(m_connection.Fetch(fetchStmt))
Catch ex As SoapException
Call FireError_(ex.Detail.InnerXml)
Catch ex As Exception
Call FireError_(ex.Message)
End Try
Dim recsList As XmlNodeList = doc.DocumentElement.ChildNodes
If recsList.Count > 0 Then
result = New String(m_valueIdx.Length - 1) {}
For Each element As XmlElement In recsList(0).ChildNodes
Dim colIndex As Object = m_attrToIdx(element.Name)
If colIndex Is Nothing Then
Continue For
End If
result(CInt(colIndex)) = element.InnerText
Next
End If
GetLookupRow_ = result
End Function ' GetLookupRow_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function GetAttributes_() As ICrmAttribute()
Dim result() As ICrmAttribute = m_attributes
If result Is Nothing Then
Dim connection As ICrmConnection = CType( _
Me.DesignConnections(Me.CrmConnection).AcquireConnection(Nothing), _
ICrmConnection)
Call connection.Connect()
Try
result = connection.GetEntityAttributes(Me.Entity)
Finally
Call connection.Close()
End Try
m_attributes = result
End If
GetAttributes_ = result
End Function ' GetAttributes_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub SetupOutputColumns_()
' Get entity attributes.
Dim attrList() As ICrmAttribute = GetAttributes_()
Dim valueList() As String = Me.ValueAttribute.Split( _
New String() {ListDelimiter}, _
StringSplitOptions.None)
Dim output As IDTSOutput100 = Me.ComponentMetaData.OutputCollection(0)
For Each value As String In valueList
' Find CRM attribute.
Dim attr As ICrmAttribute = FindAttribute_(attrList, value)
' Setup output column.
Dim column As IDTSOutputColumn100 = output.OutputColumnCollection.[New]()
column.Name = attr.Name
Call column.SetDataTypeProperties( _
CType(attr.AttributeType, DataType), _
attr.Length, _
attr.Precision, _
attr.Scale, _
0)
Next
End Sub ' SetupOutputColumns_
#End Region ' Internals
#Region "Attributes"
Private m_crmConnection As String
Private m_entity As String
Private m_keyAttribute As String
Private m_valueAttribute As String
Private m_useCache As Boolean
Private m_connection As ICrmConnection
Private m_cache As Hashtable
Private m_fetchTemplate_1P As String
Private m_keyIdx() As Integer
Private m_condTemplate_P As String
Private m_valueIdx() As Integer
Private m_attributes() As ICrmAttribute
Private m_attrToIdx As Hashtable
Private m_valueType() As DataType
#End Region ' Attributes
End Class ' ScriptMain
]]> _
Public Class InputBuffer
Inherits ScriptBufferPlus
Public Sub New(ByVal Component As ScriptComponent, ByVal ObjectID As Integer, ByVal IsInput As Boolean, ByVal Buffer As PipelineBuffer, ByVal OutputMap As OutputNameMap)
MyBase.New(Component, ObjectID, IsInput, Buffer, OutputMap)
End Sub
Public Overrides ReadOnly Property StaticInputColumns() As String()
Get
Return New String() {}
End Get
End Property
Public WriteOnly Property [telephone1]() As String
Set
Me(0) = Value
End Set
End Property
Public WriteOnly Property [telephone1_IsNull] As Boolean
Set
If (value)
SetNull(0)
Else
Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.")
End If
End Set
End Property
Public WriteOnly Property [name]() As String
Set
Me(1) = Value
End Set
End Property
Public WriteOnly Property [name_IsNull] As Boolean
Set
If (value)
SetNull(1)
Else
Throw new InvalidOperationException("IsNull property cannot be set to False. Assign a value to the column instead.")
End If
End Set
End Property
Public Overrides ReadOnly Property StaticOutputColumns() As String()
Get
Return New String() {"telephone1", "name"}
End Get
End Property
Public Overloads Function NextRow() As Boolean
NextRow = MyBase.NextRow()
End Function
Public Overloads Function EndOfRowset() As Boolean
EndOfRowset = MyBase.EndOfRowset
End Function
End Class
]]>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}DebugAnyCPU8.0.307032.0{1BC34149-F108-4746-B621-4362A98FDAE5}LibraryMy ProjectScriptComponent_a8db124b16d04c44952f3e9b4633e605ScriptComponent_a8db124b16d04c44952f3e9b4633e605v4.0512BinaryOnOntruefullfalse.\bin\Debug\falsetruetrueprompt4falsetrue.\bin\Release\falsefalsetrueprompt4FalseC:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\CozyRoc.SSISPlus.2014.dllFalseFalseFalseFalseFalseSSIS_SC120]]> _
Public Class UserComponent
Inherits ScriptComponentPlus
Public Connections As New Connections(Me)
Public Variables As New Variables(Me)
Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal InputName As String, ByVal Buffer As PipelineBuffer, ByVal OutputMap As OutputNameMap)
If InputID = MyBase.ComponentMetaData.InputCollection("Input").ID Then
Input_ProcessInput(New InputBuffer(Me, InputID, True, Buffer, OutputMap))
End If
End Sub
Public Overridable Sub Input_ProcessInput(ByVal Buffer As InputBuffer)
While Buffer.NextRow()
Input_ProcessInputRow(Buffer)
End While
End Sub
Public Overridable Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
End Sub
End Class
Public Class Connections
Dim ParentComponent As ScriptComponent
_
Public Sub New(ByVal Component As ScriptComponent)
ParentComponent = Component
End Sub
Public ReadOnly Property CrmConnection() As IDTSConnectionManager100
Get
Return ParentComponent.ComponentMetaData.RuntimeConnectionCollection("CrmConnection").ConnectionManager
End Get
End Property
End Class
Public Class Variables
Dim ParentComponent As ScriptComponent
_
Public Sub New(ByVal Component As ScriptComponent)
ParentComponent = Component
End Sub
End Class
]]>msBuildScriptComponent_a8db124b16d04c44952f3e9b4633e605ScriptComponent_a8db124b16d04c44952f3e9b4633e605{1E8B9B1A-1B5D-492D-AEF1-7313E60E8ADC}]]>CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus.2014, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34eaScriptComponent_a8db124b16d04c44952f3e9b4633e605VisualBasic