]]> _
_
_
Public Class ScriptMain
Inherits UserComponent
Public Const StatusCode As String = "statuscode"
Public Enum NonMatchAction
Create
Replace
Nullify
Ignore
RaiseError
End Enum ' NonMatchAction
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Try
Dim label As String = Convert.ToString(GetBufferValue_(Row.Buffer, m_lcIdx))
If Not m_dictionary Is Nothing Then
' Mapping specified. Use it.
label = Convert.ToString(m_dictionary(label))
End If
If String.IsNullOrEmpty(label) Then
Exit Sub
End If
Dim value As Object = m_values(label)
If Not value Is Nothing Then
Row.Buffer(m_vcIdx) = Convert.ToInt32(value)
Else
' Input option value not found.
Select Case Me.Action
Case NonMatchAction.Create
Call CreateOptionSet_(Row)
Case NonMatchAction.Replace
Row.Buffer(m_vcIdx) = Convert.ToInt32(Me.ReplaceValue)
Case NonMatchAction.Nullify
Call Row.Buffer.SetNull(m_vcIdx)
Case NonMatchAction.RaiseError
Throw New Exception(String.Format("OptionSet label not found: {0}", label))
End Select
End If
Catch ex As SoapException
Call FireError_(ex.Detail.InnerXml)
Catch ex As Exception
Call FireError_(ex.Message)
End Try
End Sub ' Input_ProcessInputRow
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PreExecute()
Call MyBase.PreExecute()
' Setup CRM service.
m_connection = CType( _
Me.Connections.CrmConnection.AcquireConnection(Nothing), _
IConnection)
Call m_connection.Connect()
' Load OptionSet values.
m_values = GetOptionSetValues_()
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
' Get input columns index.
m_lcIdx = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
input.InputColumnCollection(Me.LabelColumn).LineageID)
m_vcIdx = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
input.InputColumnCollection(Me.ValueColumn).LineageID)
If Not String.IsNullOrEmpty(Me.StateCodeColumn) Then
m_sccIdx = Me.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
input.InputColumnCollection(Me.StateCodeColumn).LineageID)
End If
If Not String.IsNullOrEmpty(Me.Mapping) Then
' Mapping specified. Load it.
m_dictionary = LoadMapping_()
End If
End Sub ' PreExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PostExecute()
Call MyBase.PostExecute()
Call m_connection.Close()
m_values = Nothing
m_dictionary = Nothing
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 Exception("Select Dynamics CRM Connection.")
End If
If String.IsNullOrEmpty(Me.Entity) Then
Throw New Exception("Select Dynamics CRM entity.")
End If
If String.IsNullOrEmpty(Me.Attribute) Then
Throw New Exception("Select OptionSet attribute.")
End If
Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0)
If input.IsAttached Then
If String.IsNullOrEmpty(Me.LabelColumn) Then
Throw New Exception("Select label column.")
End If
If String.IsNullOrEmpty(Me.ValueColumn) Then
Throw New Exception("Select value column.")
End If
If Me.Action = NonMatchAction.Create AndAlso _
Me.Attribute.Equals(StatusCode, StringComparison.OrdinalIgnoreCase) AndAlso _
String.IsNullOrEmpty(Me.StateCodeColumn) Then
Throw New Exception("Select statecode column.")
End If
' Setup used input columns.
Call input.InputColumnCollection.RemoveAll()
Dim virtInput As IDTSVirtualInput90 = input.GetVirtualInput()
Dim virtColl As IDTSVirtualInputColumnCollection90 = virtInput.VirtualInputColumnCollection
Call virtInput.SetUsageType( _
virtColl(Me.LabelColumn).LineageID, _
DTSUsageType.UT_READONLY)
Call virtInput.SetUsageType( _
virtColl(Me.ValueColumn).LineageID, _
DTSUsageType.UT_READWRITE)
If Not String.IsNullOrEmpty(Me.StateCodeColumn) Then
Call virtInput.SetUsageType( _
virtColl(Me.StateCodeColumn).LineageID, _
DTSUsageType.UT_READONLY)
End If
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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of entities in the currently selected Dynamics CRM connection.
Public Function GetEntityList() As String()
Dim result() As String
Dim connection As IConnection = CType( _
Me.DesignConnections(Me.CrmConnection).AcquireConnection(Nothing), _
IConnection)
Call connection.Connect()
Try
result = connection.GetEntityList(2) ' 0 - None
Call Array.Sort(result)
Finally
Call connection.Close()
End Try
GetEntityList = result
End Function ' GetEntityList
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of OptionSet attributes in the currently selected Dynamics CRM entity.
Public Function GetOptionSetList() As String()
If String.IsNullOrEmpty(Me.Entity) Then
Throw New Exception("Select Dynamics CRM Entity.")
End If
If m_attributes.Contains(Me.Entity) Then
Return CType(m_attributes(Me.Entity), String())
End If
' Load entity's OptionSet attributes.
Dim result As New ArrayList()
Dim connection As IConnection = CType( _
Me.DesignConnections(Me.CrmConnection).AcquireConnection(Nothing), _
IConnection)
Call connection.Connect()
Try
Dim service As Object = connection.GetService(0) ' 0 - CRM Service
' 11 - AttributeTypeCode.Picklist (CRM 2011)
' 12 - AttributeType.Picklist (CRM 4)
Dim pickListType As Integer = Convert.ToInt32(IIf(TypeOf service Is OrganizationServiceClient, 11, 12))
' 13 - AttributeTypeCode.Status (CRM 2011)
' 15 - AttributeType.Status (CRM 4)
Dim statusType As Integer = Convert.ToInt32(IIf(TypeOf service Is OrganizationServiceClient, 13, 15))
Dim entity As IEntity = connection.GetEntity(Me.Entity)
Dim attributes() As IAttribute = entity.GetAttributes()
For Each attribute As IAttribute In attributes
If attribute.CrmType = pickListType OrElse attribute.CrmType = statusType Then
Call result.Add(attribute.Name)
End If
Next
Call result.Sort()
Finally
Call connection.Close()
End Try
m_attributes(Me.Entity) = result.ToArray(GetType(String))
GetOptionSetList = CType(m_attributes(Me.Entity), String())
End Function ' GetOptionSetList
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns list of current properties based on current state.
Public Function GetProperties() As String()
Dim result As New ArrayList
' Setup static.
Call result.Add("CrmConnection")
Call result.Add("Entity")
Call result.Add("Attribute")
Call result.Add("LabelColumn")
Call result.Add("ValueColumn")
Call result.Add("Mapping")
Call result.Add("Action")
' Setup dynamic.
Select Case Me.Action
Case NonMatchAction.Create
If String.Equals( _
Me.Attribute, _
StatusCode, _
StringComparison.OrdinalIgnoreCase) Then
Call result.Add("StateCodeColumn")
End If
Case NonMatchAction.Replace
Call result.Add("ReplaceValue")
End Select
Return CType(result.ToArray(GetType(String)), String())
End Function ' GetProperties
#Region "Properties"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
_
Public Property CrmConnection() As String
Get
CrmConnection = m_crmConnection
End Get
Set(ByVal value As String)
If m_crmConnection <> value Then
m_crmConnection = value
Me.Entity = String.Empty
End If
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
Me.Attribute = String.Empty
End If
End Set
End Property ' Entity
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
_
Public Property Attribute() As String
Get
Attribute = m_attribute
End Get
Set(ByVal value As String)
m_attribute = value
End Set
End Property ' Attribute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property LabelColumn() As String
Get
LabelColumn = m_labelCol
End Get
Set(ByVal value As String)
m_labelCol = value
End Set
End Property ' LabelColumn
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property ValueColumn() As String
Get
ValueColumn = m_valueCol
End Get
Set(ByVal value As String)
m_valueCol = value
End Set
End Property ' ValueColumn
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property Mapping() As String
Get
Mapping = m_mapping
End Get
Set(ByVal value As String)
m_mapping = value
End Set
End Property ' Mapping
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property Action() As NonMatchAction
Get
Action = m_action
End Get
Set(ByVal value As NonMatchAction)
m_action = value
End Set
End Property ' Action
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
Public Property ReplaceValue() As Integer
Get
ReplaceValue = m_replaceValue
End Get
Set(ByVal value As Integer)
m_replaceValue = value
End Set
End Property ' ReplaceValue
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
_
_
_
Public Property StateCodeColumn() As String
Get
StateCodeColumn = m_stateCodeCol
End Get
Set(ByVal value As String)
m_stateCodeCol = value
End Set
End Property ' StateCodeColumn
#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 OptionSet", _
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 GetBufferValue_( _
ByVal buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer, _
ByVal index As Integer) As Object
Dim result As Object = Nothing
If index <> -1 AndAlso Not buffer.IsNull(index) Then
result = buffer(index)
End If
GetBufferValue_ = result
End Function ' GetBufferValue_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Load OptionSet attribute values.
Private Function GetOptionSetValues_() As Hashtable
Dim result As New Hashtable
Dim service As Object = m_connection.GetService(0) ' 0 - CRM Service
Dim service2011 As OrganizationServiceClient = TryCast( _
service, _
OrganizationServiceClient)
If Not service2011 Is Nothing Then
' CRM 2011.
Dim req As New OrganizationRequest()
req.RequestName = "RetrieveAttribute"
req("MetadataId") = New Guid()
req("RetrieveAsIfPublished") = False
req("EntityLogicalName") = Me.Entity
req("LogicalName") = Me.Attribute
Dim res As OrganizationResponse = service2011.Execute(req)
Dim enumMeta As EnumAttributeMetadata = CType( _
res("AttributeMetadata"), _
EnumAttributeMetadata)
For Each optionMeta As OptionMetadata In enumMeta.OptionSet.Options
' Find invariant label.
Dim invariantIndex As Integer = -1
Dim count As Integer = optionMeta.Label.LocalizedLabels.Count
For index As Integer = 0 To count - 1
Dim label As LocalizedLabel = optionMeta.Label.LocalizedLabels(index)
If label.LanguageCode = 1033 Then
invariantIndex = index
Exit For
End If
Next
If invariantIndex = -1 Then
' Invariant label not found.
invariantIndex = 0
End If
result(optionMeta.Label.LocalizedLabels(invariantIndex).Label) = optionMeta.Value.Value
Next
Else
Dim metadata As Object = m_connection.GetService(1) ' 1 - Metadata Service.
Dim metadata4 As Metadata4.MetadataService = TryCast( _
metadata, _
Metadata4.MetadataService)
If Not metadata4 Is Nothing Then
' CRM 4.
Dim req4 As New Metadata4.RetrieveAttributeRequest()
req4.EntityLogicalName = Me.Entity
req4.LogicalName = Me.Attribute
Dim res4 As Metadata4.RetrieveAttributeResponse = CType( _
metadata4.Execute(req4), _
Metadata4.RetrieveAttributeResponse)
Dim options4() As Metadata4.Option = CType( _
res4.AttributeMetadata, _
Metadata4.PicklistAttributeMetadata).Options
For Each option4 As Metadata4.Option In options4
' Find invariant label.
Dim invariantIndex As Integer = -1
Dim count As Integer = option4.Label.LocLabels.Length
For index As Integer = 0 To count - 1
Dim label As Metadata4.LocLabel = option4.Label.LocLabels(index)
If label.LanguageCode.Value = 1033 Then
invariantIndex = index
Exit For
End If
Next
If invariantIndex = -1 Then
' Invariant label not found.
invariantIndex = 0
End If
result(option4.Label.LocLabels(invariantIndex).Label) = option4.Value.Value
Next
Else
Dim metadata3 As Metadata3.MetadataService = TryCast(metadata, Metadata3.MetadataService)
If Not metadata3 Is Nothing Then
' CRM 3.
Dim options3() As Metadata3.Option = CType( _
metadata3.RetrieveAttributeMetadata(Me.Entity, Me.Attribute), _
Metadata3.PicklistAttributeMetadata).Options
For Each option3 As Metadata3.Option In options3
result(option3.Description) = option3.OptionValue
Next
Else
Throw New Exception("Unhandled service object.")
End If
End If
End If
GetOptionSetValues_ = result
End Function ' GetOptionSetValues_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Create OptionSet for specifed input and output columns.
Private Sub CreateOptionSet_(ByVal row As InputBuffer)
Dim name As String = Convert.ToString(GetBufferValue_(row.Buffer, m_lcIdx))
Dim value As Object = GetBufferValue_(row.Buffer, m_vcIdx)
Dim isStatusCode As Boolean = Me.Attribute.Equals( _
StatusCode, _
StringComparison.OrdinalIgnoreCase)
Dim service As Object = m_connection.GetService(0) ' 0 - CRM Service
Dim service2011 As OrganizationServiceClient = TryCast( _
service, _
OrganizationServiceClient)
If Not service2011 Is Nothing Then
' CRM 2011.
Dim req As New OrganizationRequest()
req.RequestName = Convert.ToString(IIf( _
isStatusCode, _
"InsertOptionValue", _
"InsertStatusValue"))
req("EntityLogicalName") = Me.Entity
req("AttributeLogicalName") = Me.Attribute
' Setup label.
Dim locLabel2011 As New LocalizedLabel()
locLabel2011.Label = name
locLabel2011.LanguageCode = 1033
Dim label2011 As New Label()
label2011.LocalizedLabels = New LocalizedLabelCollection()
label2011.LocalizedLabels.Add(locLabel2011)
req("Label") = label2011
If isStatusCode Then
req("StateCode") = Convert.ToInt32(GetBufferValue_(row.Buffer, m_sccIdx))
End If
If Not value Is Nothing Then
' Value provided. Use it.
req("Value") = Convert.ToInt32(value)
End If
Dim res2011 As OrganizationResponse = service2011.Execute(req)
If value Is Nothing Then
' Set new OptionSet value.
row.Buffer(m_vcIdx) = res2011("NewOptionValue")
End If
Else
Dim metadata As Object = m_connection.GetService(1) ' 1 - Metadata Service.
Dim metadata4 As Metadata4.MetadataService = TryCast( _
metadata, _
Metadata4.MetadataService)
If Not metadata4 Is Nothing Then
' CRM 4.
' Setup label.
Dim locLabel4 As New Metadata4.LocLabel()
locLabel4.Label = name
locLabel4.LanguageCode = New Metadata4.CrmNumber()
locLabel4.LanguageCode.Value = 1033
If isStatusCode Then
Dim isvReq As New Metadata4.InsertStatusValueRequest()
isvReq.EntityLogicalName = Me.Entity
isvReq.AttributeLogicalName = Me.Attribute
isvReq.Label = New Metadata4.CrmLabel()
isvReq.Label.LocLabels = New Metadata4.LocLabel() {locLabel4}
isvReq.StateCode = New Metadata4.CrmNumber()
isvReq.StateCode.Value = Convert.ToInt32(GetBufferValue_(row.Buffer, m_sccIdx))
If Not value Is Nothing Then
' Value provided. Use it.
isvReq.Value = New Metadata4.CrmNumber()
isvReq.Value.Value = Convert.ToInt32(value)
End If
Dim isvRes As Metadata4.InsertStatusValueResponse = CType( _
metadata4.Execute(isvReq), _
Metadata4.InsertStatusValueResponse)
If value Is Nothing Then
' Set new OptionSet value.
row.Buffer(m_vcIdx) = isvRes.NewOptionValue
End If
Else
Dim iovReq As New Metadata4.InsertOptionValueRequest()
iovReq.EntityLogicalName = Me.Entity
iovReq.AttributeLogicalName = Me.Attribute
iovReq.Label = New Metadata4.CrmLabel()
iovReq.Label.LocLabels = New Metadata4.LocLabel() {locLabel4}
If Not value Is Nothing Then
' Value provided. Use it.
iovReq.Value = New Metadata4.CrmNumber()
iovReq.Value.Value = Convert.ToInt32(value)
End If
Dim iovRes As Metadata4.InsertOptionValueResponse = CType( _
metadata4.Execute(iovReq), _
Metadata4.InsertOptionValueResponse)
If value Is Nothing Then
' Set new OptionSet value.
row.Buffer(m_vcIdx) = iovRes.NewOptionValue
End If
End If
Else
Dim metadata3 As Metadata3.MetadataService = TryCast(metadata, Metadata3.MetadataService)
If Not metadata3 Is Nothing Then
' CRM 3 - doesn't support it.
Throw New Exception("Dynamics CRM 3 doesn't support OptionSet value insert.")
Else
Throw New Exception("Unhandled service object.")
End If
End If
End If
End Sub ' CreateOptionSet_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function LoadMapping_() As IDictionary
Dim mapping As Object = GetVariable_(Me.Mapping)
Dim mappingDict As New Hashtable
Dim result As IDictionary = mappingDict
If TypeOf mapping Is IDictionary Then
result = CType(mapping, IDictionary)
ElseIf TypeOf mapping Is IDictionary(Of String, String) Then
Dim genericDict As IDictionary(Of String, String) = CType( _
mapping, _
IDictionary(Of String, String))
For Each pair As KeyValuePair(Of String, String) In genericDict
mappingDict(pair.Key) = pair.Value
Next
ElseIf TypeOf mapping Is String Then
' Mappings are separated with [CR][LF].
' Each mapping is combination [key]=[value].
Dim mappingList() As String = Regex.Unescape(CStr(mapping)).Split( _
New String() {"\r\n"}, _
StringSplitOptions.RemoveEmptyEntries)
For Each pair As String In mappingList
Dim keyValue() As String = pair.Split(New Char() {Chr(61)}, 2) ' 61 (=)
If (keyValue.Length < 2) Then
' Not a valid item.
Continue For
End If
mappingDict(keyValue(0)) = keyValue(1)
Next
ElseIf TypeOf mapping Is Recordset Then
' Mapping is recordset. Use first and second fields.
Dim rs As Recordset = CType(mapping, Recordset)
If rs.Fields.Count < 2 Then
Throw New Exception("Mapping.Recordset should contain at least 2 columns.")
End If
If Not (rs.BOF AndAlso rs.EOF) Then
Call rs.MoveFirst()
End If
While Not rs.EOF
mappingDict(Convert.ToString(rs.Fields(0).Value)) = Convert.ToString(rs.Fields(1).Value)
Call rs.MoveNext()
End While
ElseIf TypeOf mapping Is DataSet Then
' Variable contains ADO.NET DataSet. Use first and second fields.
Dim dt As DataTable = CType(mapping, DataSet).Tables(0)
If dt.Columns.Count < 2 Then
Throw New Exception("Mapping.DataSet should contain at least 2 columns.")
End If
For Each row As DataRow In dt.Rows
mappingDict(row(0)) = row(1)
Next
Else
Throw New Exception("Mapping parameter must contain IDictionary, String, Recordset or DataSet.")
End If
LoadMapping_ = result
End Function ' LoadMapping_
#End Region ' Internals
#Region "Attributes"
Private m_crmConnection As String
Private m_entity As String
Private m_attribute As String
Private m_labelCol As String
Private m_valueCol As String
Private m_mapping As String
Private m_action As NonMatchAction
Private m_replaceValue As Integer
Private m_stateCodeCol As String
Private m_connection As IConnection
Private m_attributes As New Hashtable
Private m_values As Hashtable
Private m_lcIdx As Integer
Private m_vcIdx As Integer
Private m_sccIdx As Integer
Private m_dictionary As IDictionary
#End Region ' Attributes
End Class ' ScriptMain
]]>ScriptComponent_86882f75d4f445daa179264a79727cf1trueCozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea