]]> _ _ _ Public Class ScriptMain Inherits UserComponent Public Const Formatted As String = "Formatted" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer) Try ' Setup entity reference. Dim entityRef As New EntityReference() entityRef.LogicalName = Convert.ToString(Row.Buffer(m_entityIdx)) entityRef.Id = New Guid(Convert.ToString(Row.Buffer(m_idIdx))) ' Setup paging info. Dim pi As PagingInfo = New PagingInfo() pi.Count = Me.BatchSize pi.PageNumber = 1 ' Setup audit request. Dim req As New OrganizationRequest() req.RequestName = "RetrieveRecordChangeHistory" req("Target") = entityRef req("PagingInfo") = pi While True Dim res As OrganizationResponse = m_service.Execute(req) Dim details As AuditDetailCollection = CType( _ res("AuditDetailCollection"), _ AuditDetailCollection) Call OutputDetails_(details) If details.MoreRecords Then ' Setup next PageInfo. pi.PageNumber += 1 pi.PagingCookie = details.PagingCookie Else ' Reached end. Exit loop. Exit While End If End While If Me.InsertTerminator Then Call OutputBuffer.AddRow() End If If Row.EndOfRowset() Then Call OutputBuffer.SetEndOfRowset() End If Catch ex As SoapException Call FireError_(ex.Detail.InnerXml) Catch ex As Exception Call FireError_(ex.ToString()) 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() m_service = CType(m_connection.GetService(0), OrganizationServiceClient) Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0) Dim inputCol As IDTSInputColumn90 ' Get input column indexes. inputCol = GetInputColumn_("Entity") m_entityIdx = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ inputCol.LineageID) inputCol = GetInputColumn_("Id") m_idIdx = Me.HostComponent.BufferManager.FindColumnByLineageID( _ input.Buffer, _ inputCol.LineageID) 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 Exception("Select Dynamics CRM Connection.") End If Dim input As IDTSInput90 = Me.ComponentMetaData.InputCollection(0) If input.IsAttached AndAlso _ (input.InputColumnCollection.Count = 0 OrElse _ input.InputColumnCollection.Count < 2) 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 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 Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0) ' Cleanup. input.ExternalMetadataColumnCollection.IsUsed = True Call input.InputColumnCollection.RemoveAll() Call input.ExternalMetadataColumnCollection.RemoveAll() Dim column As IDTSExternalMetadataColumn90 ' Setup entity. column = input.ExternalMetadataColumnCollection.[New]() column.Name = "Entity" column.DataType = DataType.DT_WSTR column.Length = 100 column = input.ExternalMetadataColumnCollection.[New]() column.Name = "Id" column.DataType = DataType.DT_GUID 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 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' _ _ _ 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 InsertTerminator() As Boolean Get InsertTerminator = m_insertTermintator End Get Set(ByVal value As Boolean) m_insertTermintator = value End Set End Property ' InsertTerminator #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 Audit Logs Query", _ message, _ String.Empty, _ 0, _ cancel) End Sub ' FireError_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Find input column of specified external column name. Private Function GetInputColumn_(ByVal externalName As String) As IDTSInputColumn90 Dim result As IDTSInputColumn90 = Nothing Dim input As IDTSInput90 = MyBase.ComponentMetaData.InputCollection(0) Dim extColumn As IDTSExternalMetadataColumn90 = input.ExternalMetadataColumnCollection(externalName) ' Find input column for specified external column. For Each inputCol As IDTSInputColumn90 In input.InputColumnCollection If inputCol.ExternalMetadataColumnID = extColumn.ID Then ' Found input column. result = inputCol Exit For End If Next If result Is Nothing Then Throw New Exception("Input column not found.") End If GetInputColumn_ = result End Function ' GetInputColumn_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Audit details output. Private Sub OutputDetails_(ByVal details As AuditDetailCollection) For Each detail As AuditDetail In details.AuditDetails If Not TypeOf detail Is AttributeAuditDetail Then Continue For End If Dim attributeDetail As AttributeAuditDetail = CType(detail, AttributeAuditDetail) ' Setup audit. Dim audit As Hashtable = GetAttributes_(detail.AuditRecord) Dim newAttrs As Hashtable = GetAttributes_(attributeDetail.NewValue) Dim oldAttrs As Hashtable = GetAttributes_(attributeDetail.OldValue) If Not attributeDetail.NewValue Is Nothing Then For Each attribute As KeyValuePair(Of String, Object) In attributeDetail.NewValue.Attributes Call AddOutputRow_(audit) With OutputBuffer .fieldName = attribute.Key .oldValue = Convert.ToString(oldAttrs(attribute.Key)) .oldValueFormatted = Convert.ToString(oldAttrs(attribute.Key + Formatted)) .newValue = Convert.ToString(newAttrs(attribute.Key)) .newValueFormatted = Convert.ToString(newAttrs(attribute.Key + Formatted)) End With Next End If If Not attributeDetail.OldValue Is Nothing Then For Each attribute As KeyValuePair(Of String, Object) In attributeDetail.OldValue.Attributes If Not newAttrs.Contains(attribute.Key) Then Call AddOutputRow_(audit) With OutputBuffer .fieldName = attribute.Key .oldValue = Convert.ToString(oldAttrs(attribute.Key)) .oldValueFormatted = Convert.ToString(oldAttrs(attribute.Key + Formatted)) End With End If Next End If Next End Sub ' OutputDetails_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetAttributes_(ByVal entity As Entity) As Hashtable Dim result As New Hashtable If entity Is Nothing Then Return result End If For Each attribute As KeyValuePair(Of String, Object) In entity.Attributes Dim value As Object = attribute.Value Dim formattedValue As Object = attribute.Value Dim entityRef As EntityReference = TryCast(value, EntityReference) Dim osv As OptionSetValue = TryCast(value, OptionSetValue) If Not entityRef Is Nothing Then value = entityRef.Id formattedValue = entityRef.Name ElseIf Not osv Is Nothing Then value = osv.Value formattedValue = GetAttributeLabel_(osv.Value) End If result(attribute.Key) = value result(attribute.Key + Formatted) = formattedValue Next For Each attribute As KeyValuePair(Of String, String) In entity.FormattedValues result(attribute.Key + Formatted) = attribute.Value Next GetAttributes_ = result End Function ' GetAttributes_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub AddOutputRow_(ByVal audit As Hashtable) With OutputBuffer Call .AddRow() .action = Convert.ToInt32(audit("action")) .actionFormatted = Convert.ToString(audit("action" + Formatted)) .auditid = New Guid(Convert.ToString(audit("auditid"))) .createdon = Convert.ToDateTime(audit("createdon")) .objectid = New Guid(Convert.ToString(audit("objectid"))) .objectidFormatted = Convert.ToString(audit("objectid" + Formatted)) .objecttypecode = Convert.ToString(audit("objecttypecode")) .objecttypecodeFormatted = Convert.ToString(audit("objecttypecode" + Formatted)) .operation = Convert.ToInt32(audit("operation")) .operationFormatted = Convert.ToString(audit("operation" + Formatted)) .userid = New Guid(Convert.ToString(audit("userid"))) .useridFormatted = Convert.ToString(audit("userid" + Formatted)) End With End Sub ' AddOutputRow_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns attribute label for input code. Most probably not needed. ' DO NOTHING FOR NOW. Private Function GetAttributeLabel_(ByVal code As Integer) As String Dim result As String = String.Empty GetAttributeLabel_ = result End Function ' GetAttributeLabel_ #End Region ' Internals #Region "Attributes" Private m_crmConnection As String Private m_batchSize As Integer Private m_insertTermintator As Boolean Private m_connection As IConnection Private m_service As OrganizationServiceClient Private m_entityIdx As Integer Private m_idIdx As Integer #End Region ' Attributes End Class ' ScriptMain ]]> ScriptComponent_a2ffa1368c5a4ba8afa29fe5da355496 true CozyRoc.SqlServer.SSIS.ScriptComponentHostPlus, CozyRoc.SSISPlus, Version=1.0.0.0, Culture=neutral, PublicKeyToken=16cf490bb80c34ea