]]> _ _ Public Class ScriptMain Inherits UserComponent Public Const ListDelimiter As String = "," + vbLf ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub CreateNewOutputRows() Dim connection As IConnection = CType( _ Me.Connections.CrmConnection.AcquireConnection(Nothing), _ IConnection) Dim colsIdx As Hashtable = GetColumnsIdx_() Try Call connection.Connect() ' 0 - CRM Service Dim service As OrganizationServiceClient = CType( _ connection.GetService(0), _ OrganizationServiceClient) ' Setup request. Dim req As New OrganizationRequest() req.RequestName = "RetrieveEntityChanges" req("EntityName") = Me.Entity Dim cs As ColumnSet = New ColumnSet() cs.Columns = Me.Attributes.Split( _ New String() {ListDelimiter}, _ StringSplitOptions.None) req("Columns") = cs Dim pi As PagingInfo = New PagingInfo() pi.Count = Me.BatchSize pi.PageNumber = 1 req("PageInfo") = pi req("DataVersion") = Convert.ToString(GetVariable_(Me.TokenVariable)) While True Dim res As OrganizationResponse = service.Execute(req) Dim entityChanges As BusinessEntityChanges = CType( _ res("EntityChanges"), _ BusinessEntityChanges) ' Process entity changes. For Each change As Object In entityChanges.Changes Call MyBase.OutputBuffer.AddRow() Dim newUpdated As NewOrUpdatedItem = TryCast(change, NewOrUpdatedItem) Dim deleted As RemovedOrDeletedItem = TryCast(change, RemovedOrDeletedItem) If Not newUpdated Is Nothing Then ' New or updated. MyBase.OutputBuffer.Buffer(CType(colsIdx("_Type"), Integer)) = "updated" MyBase.OutputBuffer.Buffer(CType(colsIdx("Id"), Integer)) = newUpdated.NewOrUpdatedEntity.Id For Each pair As KeyValuePair(Of String, Object) In newUpdated.NewOrUpdatedEntity.Attributes If Not colsIdx.Contains(pair.Key) Then Continue For End If Try MyBase.OutputBuffer.Buffer(CType(colsIdx(pair.Key), Integer)) = pair.Value Catch ex As Exception Call FireError_(String.Format( _ "Failed to process column '{0}': {1}", _ pair.Key, _ pair.Value)) End Try Next ElseIf Not deleted Is Nothing Then ' Deleted. MyBase.OutputBuffer.Buffer(CType(colsIdx("_Type"), Integer)) = "deleted" MyBase.OutputBuffer.Buffer(CType(colsIdx("Id"), Integer)) = deleted.RemovedItem.Id End If Next If entityChanges.MoreRecords Then ' Setup next PageInfo. pi.PageNumber += 1 pi.PagingCookie = entityChanges.PagingCookie Else ' Reached end. Store token and exit loop. SetVariable_(Me.TokenVariable, entityChanges.DataToken) Exit While End If End While Call MyBase.OutputBuffer.SetEndOfRowset() Catch ex As Exception Call FireError_(ex.Message) Finally Call connection.Close() End Try End Sub ' CreateNewOutputRows ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 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.Attributes) Then Throw New Exception("Select attribute(s).") End If If String.IsNullOrEmpty(Me.TokenVariable) Then Throw New Exception("Select token variable.") End If Dim output As IDTSOutput90 = MyBase.ComponentMetaData.OutputCollection(0) If output.OutputColumnCollection.Count = 0 Then ' Setup output columns collection. 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 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 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) ' 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 IAttribute = GetAttributes_() For Each attr As IAttribute In attrList Call result.Add(attr.Name) Next Call result.Sort() GetAttributeList = CType(result.ToArray(GetType(String)), String()) End Function ' GetAttributeList ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetAttributes_() As IAttribute() If Not m_entityAttributes Is Nothing Then Return m_entityAttributes End If ' Cache entity attributes. Dim connection As IConnection = CType( _ Me.DesignConnections(Me.CrmConnection).AcquireConnection(Nothing), _ IConnection) Call connection.Connect() Try Dim entity As IEntity = connection.GetEntity(Me.Entity) m_entityAttributes = entity.GetAttributes() Finally Call connection.Close() End Try GetAttributes_ = m_entityAttributes End Function ' GetAttributes_ #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 If Not String.IsNullOrEmpty(m_entity) Then ' Reset selected attributes. Me.Attributes = String.Empty End If m_entity = value End If End Set End Property ' Entity ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property Attributes() As String Get Attributes = m_attributes End Get Set(ByVal value As String) If m_attributes <> value Then If Not String.IsNullOrEmpty(m_attributes) 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_attributes = value End If End Set End Property ' Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property BatchSize() As Integer Get BatchSize = m_batchSize End Get Set(ByVal value As Integer) m_batchSize = value End Set End Property ' BatchSize ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ Public Property TokenVariable() As String Get TokenVariable = m_tokenVariable End Get Set(ByVal value As String) m_tokenVariable = value End Set End Property ' TokenVariable #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 Entity Changes Source", _ 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 Sub SetVariable_(ByVal varName As String, ByVal value As Object) Dim vars As IDTSVariables90 = Nothing Call Me.VariableDispenser.LockOneForWrite(varName, vars) Try vars(varName).Value = value Finally Call vars.Unlock() End Try End Sub ' SetVariable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub SetupOutputColumns_() ' Get entity attributes. Dim entityAttrList() As IAttribute = GetAttributes_() Dim attrList() As String = Me.Attributes.Split( _ New String() {ListDelimiter}, _ StringSplitOptions.None) Dim output As IDTSOutput90 = Me.ComponentMetaData.OutputCollection(0) Dim column As IDTSOutputColumn90 = Nothing ' Setup entity change type column. column = output.OutputColumnCollection.[New]() column.Name = "_Type" Call column.SetDataTypeProperties(DataType.DT_WSTR, 10, 0, 0, 0) ' Setup entity identifier column. column = output.OutputColumnCollection.[New]() column.Name = "Id" Call column.SetDataTypeProperties(DataType.DT_GUID, 0, 0, 0, 0) For Each value As String In attrList ' Find CRM attribute. Dim attr As IAttribute = FindAttribute_(entityAttrList, value) ' Setup output column. column = 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_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function FindAttribute_( _ ByVal attrList() As IAttribute, _ ByVal name As String) As IAttribute Dim result As IAttribute = Nothing For Each attr As IAttribute 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_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns columns index. Private Function GetColumnsIdx_() As Hashtable Dim result As Hashtable = New Hashtable() Dim output As IDTSOutput90 = MyBase.ComponentMetaData.OutputCollection(0) Dim colsCount As Integer = output.OutputColumnCollection.Count For colIndex As Integer = 0 To colsCount - 1 Dim column As IDTSOutputColumn90 = output.OutputColumnCollection(colIndex) result(column.Name) = Me.HostComponent.BufferManager.FindColumnByLineageID( _ output.Buffer, _ column.LineageID) Next GetColumnsIdx_ = result End Function ' GetColumnsIdx_ #End Region ' Internals #Region "Attributes" Private m_crmConnection As String Private m_entity As String Private m_entityAttributes() As IAttribute Private m_attributes As String Private m_batchSize As Integer Private m_tokenVariable As String #End Region ' Attributes End Class ' ScriptMain ]]> ScriptComponent_5611ff4080ed4d32bb643cb87bc9f810 true CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea