]]> _
_
_
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 IDTSInput90 = 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 IDTSInputColumn90 = input.InputColumnCollection(colIndex)
' Set key index.
m_keyIdx(colIndex) = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
inputCol.LineageID)
' Set condition list.
Dim extCol As IDTSExternalMetadataColumn90 = input.ExternalMetadataColumnCollection.GetObjectByID( _
inputCol.ExternalMetadataColumnID)
m_condTemplate_P += String.Format( _
CondTemlate_2P, _
extCol.Name, _
"{" + colIndex.ToString() + "}")
Next
Dim output As IDTSOutput90 = 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 IDTSOutputColumn90 = 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 IDTSInput90 = 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 IDTSOutput90 = 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 IDTSInput90 = 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 IDTSExternalMetadataColumn90 = 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 IDTSInput90 = 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 IDTSOutput90 = 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 IDTSOutput90 = 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 IDTSOutputColumn90 = 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
]]>ScriptComponent_a8db124b16d04c44952f3e9b4633e605trueCozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea