]]> _
_
Public Class ScriptMain
Inherits UserComponent
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Class Consts
Public Const Opportunity As String = "opportunity"
Public Const OpportunityClose As String = "opportunityclose"
Public Const OpportunityState As String = "opportunitystatecode"
Public Const OpportunityStatus As String = "opportunitystatuscode"
Public Const ReferenceType As String = "ReferenceType"
Public Const OpportunityId As String = "opportunityid"
End Class
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
Try
If Not m_service2011 Is Nothing Then
Call Process2011_(Row.Buffer)
ElseIf Not m_service4 Is Nothing Then
Call Process4_(Row.Buffer)
Else
Call Process3_(Row.Buffer)
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), _
ICrmConnection)
Call m_connection.Connect()
Dim service As Object = m_connection.GetService(0) ' 0 - CRM Service
m_service2011 = TryCast(service, CRM2011.OrganizationServiceClient)
If m_service2011 Is Nothing Then
m_service4 = TryCast(service, CRM4.CrmService)
If m_service4 Is Nothing Then
m_service3 = TryCast(service, CRM3.CrmService)
If m_service3 Is Nothing Then
Throw New Exception("Unhandled service object.")
End If
End If
End If
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
m_closeEntity = m_connection.ConstructEntity(Consts.OpportunityClose)
' Get input column indexes.
Dim colsCount As Integer = input.InputColumnCollection.Count
m_inputIdx = New Integer(colsCount - 1) {}
For colIndex As Integer = 0 To colsCount - 1
Dim inputCol As IDTSInputColumn90 = Input.InputColumnCollection(colIndex)
Dim extColumn As IDTSExternalMetadataColumn90 = Input.ExternalMetadataColumnCollection.GetObjectByID( _
inputCol.ExternalMetadataColumnID)
If extColumn.Name = Consts.OpportunityState Then
m_stateIdx = colIndex
ElseIf extColumn.Name = Consts.OpportunityStatus Then
m_statusIdx = colIndex
Else
Dim refType As String = CStr(extColumn.CustomPropertyCollection(Consts.ReferenceType).Value)
Call m_closeEntity.SetupAttribute(extColumn.Name, refType)
End If
m_inputIdx(colIndex) = MyBase.HostComponent.BufferManager.FindColumnByLineageID( _
input.Buffer, _
inputCol.LineageID)
Next
If m_stateIdx = -1 Then
Throw New Exception(Consts.OpportunityState + " not mapped.")
End If
End Sub ' PreExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Overrides Sub PostExecute()
Call MyBase.PostExecute()
Call m_connection.Close()
m_closeEntity = Nothing
m_inputIdx = Nothing
m_stateIdx = -1
m_statusIdx = -1
End Sub ' PostExecute
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function Validate(ByRef errMessage As String) As Boolean
Dim result As Boolean = True
Try
If String.IsNullOrEmpty(Me.CrmConnection) Then
Throw New ApplicationException("Select Dynamics CRM connection.")
End If
Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0)
If input.IsAttached AndAlso input.InputColumnCollection.Count = 0 Then
Throw New Exception("Map input columns.")
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
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.CrmConnection) Then
Throw New Exception("Connection not selected.")
End If
Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0)
' Get opportunityclose attributes.
Dim connection As ICrmConnection = Nothing
Dim attributes As ICrmAttribute()
Try
Dim manager As ConnectionManager = Me.DesignConnections(Me.CrmConnection)
connection = CType(manager.AcquireConnection(Nothing), ICrmConnection)
Call connection.Connect()
attributes = connection.GetEntityAttributes(Consts.OpportunityClose)
Finally
If Not connection Is Nothing Then
Call connection.Close()
End If
End Try
' Cleanup.
input.ExternalMetadataColumnCollection.IsUsed = True
Call input.InputColumnCollection.RemoveAll()
Call input.ExternalMetadataColumnCollection.RemoveAll()
Dim extColumn As IDTSExternalMetadataColumn90
' Setup column metadata.
For Each attribute As ICrmAttribute In attributes
extColumn = input.ExternalMetadataColumnCollection.[New]()
extColumn.Name = attribute.Name
extColumn.DataType = CType(attribute.AttributeType, DataType)
extColumn.Length = attribute.Length
extColumn.Precision = attribute.Precision
extColumn.Scale = attribute.Scale
extColumn.CodePage = 0
' Setup reference type property.
Dim prop As IDTSCustomProperty90 = extColumn.CustomPropertyCollection.[New]()
prop.Name = Consts.ReferenceType
prop.Value = attribute.Reference
Next
' Include opportunity state column.
extColumn = input.ExternalMetadataColumnCollection.[New]()
extColumn.Name = Consts.OpportunityState
extColumn.DataType = DataType.DT_I4
' Include opportunity status column.
extColumn = input.ExternalMetadataColumnCollection.[New]()
extColumn.Name = Consts.OpportunityStatus
extColumn.DataType = DataType.DT_I4
End Sub ' ReinitializeMetaData
#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
#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 Opportunity State", _
message, _
String.Empty, _
0, _
cancel)
End Sub ' FireError_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub GetValues_( _
ByRef buffer As PipelineBuffer, _
ByRef state As Integer, _
ByRef status As Integer)
state = -1
status = -1
Dim entityIndex As Integer = 0
Dim colsCount As Integer = m_inputIdx.Length
For colIndex As Integer = 0 To colsCount - 1
Dim bufIndex As Integer = m_inputIdx(colIndex)
' Get value.
Dim value As Object = Nothing
If Not buffer.IsNull(bufIndex) Then
value = buffer(bufIndex)
If TypeOf value Is BlobColumn Then
value = buffer.GetString(bufIndex)
End If
End If
If colIndex = m_stateIdx Then
state = Convert.ToInt32(value)
ElseIf colIndex = m_statusIdx Then
status = Convert.ToInt32(value)
Else
m_closeEntity.SetAttributeValue(entityIndex, value)
entityIndex += 1
End If
Next
If state < 0 OrElse state > 2 Then
Throw New Exception("Unhandled opportunity state.")
End If
End Sub ' GetValues_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Process2011_(ByVal buffer As PipelineBuffer)
Dim req As CRM2011.OrganizationRequest = New CRM2011.OrganizationRequest()
Dim state, status As Integer
Call GetValues_(buffer, state, status)
Dim osv As CRM2011.OptionSetValue
' Setup status.
osv = New CRM2011.OptionSetValue()
osv.Value = status
req("Status") = osv
Select Case state
Case 0
req.RequestName = "SetState"
' Setup state.
osv = New CRM2011.OptionSetValue()
osv.Value = state
req("State") = osv
' Setup entity
Dim entityRef As CRM2011.EntityReference = New CRM2011.EntityReference()
entityRef.LogicalName = Consts.Opportunity
entityRef.Id = New Guid(m_closeEntity.GetAttributeValue(Consts.OpportunityId).ToString())
req("EntityMoniker") = entityRef
Case 1
req.RequestName = "WinOpportunity"
req("OpportunityClose") = m_closeEntity.ConstructDynamicEntity()
Case 2
req.RequestName = "LoseOpportunity"
req("OpportunityClose") = m_closeEntity.ConstructDynamicEntity()
End Select
Call m_service2011.Execute(req)
End Sub ' Process2011_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Process4_(ByVal buffer As PipelineBuffer)
Dim req As CRM4.Request = Nothing
Dim state, status As Integer
Call GetValues_(buffer, state, status)
Select Case CType(state, CRM4.OpportunityState)
Case CRM4.OpportunityState.Open
Dim stateReq As CRM4.SetStateDynamicEntityRequest = New CRM4.SetStateDynamicEntityRequest()
' Setup state.
stateReq.State = "Open"
' Setup status.
stateReq.Status = status
' Setup entity.
Dim moniker As CRM4.Moniker = New CRM4.Moniker()
moniker.Name = Consts.Opportunity
moniker.Id = New Guid(m_closeEntity.GetAttributeValue(Consts.OpportunityId).ToString())
stateReq.Entity = moniker
req = stateReq
Case CRM4.OpportunityState.Won
Dim winReq As CRM4.WinOpportunityRequest = New CRM4.WinOpportunityRequest()
winReq.Status = status
winReq.OpportunityClose = CType( _
m_closeEntity.ConstructDynamicEntity(), _
CRM4.BusinessEntity)
req = winReq
Case CRM4.OpportunityState.Lost
Dim loseReq As CRM4.LoseOpportunityRequest = New CRM4.LoseOpportunityRequest()
loseReq.Status = status
loseReq.OpportunityClose = CType( _
m_closeEntity.ConstructDynamicEntity(), _
CRM4.BusinessEntity)
req = loseReq
End Select
Call m_service4.Execute(req)
End Sub ' Process4_
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Process3_(ByVal buffer As PipelineBuffer)
Dim req As CRM3.Request = Nothing
Dim state, status As Integer
Call GetValues_(buffer, state, status)
Select Case CType(state, CRM3.OpportunityState)
Case CRM3.OpportunityState.Open
Dim stateReq As CRM3.SetStateDynamicEntityRequest = New CRM3.SetStateDynamicEntityRequest()
' Setup state.
stateReq.State = "Open"
' Setup status.
stateReq.Status = status
' Setup entity.
Dim moniker As CRM3.Moniker = New CRM3.Moniker()
moniker.Name = Consts.Opportunity
moniker.Id = New Guid(m_closeEntity.GetAttributeValue(Consts.OpportunityId).ToString())
stateReq.Entity = moniker
req = stateReq
Case CRM3.OpportunityState.Won
Dim winReq As CRM3.WinOpportunityRequest = New CRM3.WinOpportunityRequest()
winReq.Status = status
winReq.OpportunityClose = CType( _
m_closeEntity.ConstructDynamicEntity(), _
CRM3.BusinessEntity)
req = winReq
Case CRM3.OpportunityState.Lost
Dim loseReq As CRM3.LoseOpportunityRequest = New CRM3.LoseOpportunityRequest()
loseReq.Status = status
loseReq.OpportunityClose = CType( _
m_closeEntity.ConstructDynamicEntity(), _
CRM3.BusinessEntity)
req = loseReq
End Select
Call m_service3.Execute(req)
End Sub ' Process3_
#End Region ' Internals
#Region "Attributes"
Private m_crmConnection As String
Private m_connection As ICrmConnection
Private m_service2011 As CRM2011.OrganizationServiceClient
Private m_service4 As CRM4.CrmService
Private m_service3 As CRM3.CrmService
Private m_closeEntity As ICrmEntity
Private m_inputIdx() As Integer
Private m_stateIdx As Integer = -1
Private m_statusIdx As Integer = -1
#End Region ' Attributes
End Class ' ScriptMain
]]>ScriptComponent_ffa3ca46f57a4a838fb5af55c1fd8058trueCozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea