_
_
_
_
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 IDTSInput100 = 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 IDTSInputColumn100 = Input.InputColumnCollection(colIndex)
Dim extColumn As IDTSExternalMetadataColumn100 = 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 IDTSInput100 = 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 IDTSInput100 = 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 IDTSExternalMetadataColumn100
' 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 IDTSCustomProperty100 = 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
]]> _
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 Overrides ReadOnly Property StaticOutputColumns() As String()
Get
Return New String() {}
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{898FE6DD-703B-4150-938F-03E407297544}LibraryMy ProjectScriptComponent_ffa3ca46f57a4a838fb5af55c1fd8058ScriptComponent_ffa3ca46f57a4a838fb5af55c1fd8058v4.0512truefullfalse.\bin\Debug\falsetruetrueprompt4falsetrue.\bin\Release\falsefalsetrueprompt4FalseC:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\CozyRoc.SSISPlus.2016.dllSSIS_SC130BinaryOnOn]]> _
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
]]>Reference;Import;Folder{2FA443D0-0C01-4463-BE98-7554272A4463}ScriptComponent_9648c45aa14e4c9cb42ff789bc2f3fc1msBuildScriptComponent_9648c45aa14e4c9cb42ff789bc2f3fc1]]>CozyRoc.ScriptComponentHostPlusScriptComponent_9648c45aa14e4c9cb42ff789bc2f3fc1VisualBasic