none
ETL Error - Transform Job Failing. No Reporting Working

    Question

  • Hi,

    I am still getting this error on the production and test Environment. We have ran the scripts provided by Provance to try and fix the 'TransformDeviceSoftwareItemFact' error however we are still getting it as per the error below.

     

    It seems to be a problem with the Primary Key. Can you please help and if not we will raise it with Microsoft Support

    Thanks

    Sally

     

    ETL Module Execution failed:

    ETL process type: Transform

    Batch ID: 1038

    Module name: TransformDeviceInstalledSoftwareItemFact

    Message: ErrorNumber="2627" Message="Violation of PRIMARY KEY constraint 'PK_DeviceInstalledSoftwareItemFact'. Cannot insert duplicate key in object 'dbo.DeviceInstalledSoftwareItemFact_2010_Jun'." Severity="14" State="1" ProcedureName="TransformDeviceInstalledSoftwareItemFactProc" LineNumber="243" Task="Step 9: Inserting into destination Fact"

    Stack: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

    at System.Data.SqlClient.SqlDataReader.get_MetaData()

    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

    at System.Data.SqlClient.SqlCommand.ExecuteReader()

    at Microsoft.SystemCenter.Warehouse.Utility.SqlHelper.ExecuteReader(SqlConnection sqlCon, CommandType cmdType, String cmdText, SqlParameter[] parameters)

    at Microsoft.SystemCenter.Warehouse.Etl.StoredProcedure.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)

    at Microsoft.SystemCenter.Warehouse.Etl.TransformModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)

    at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)

    Sunday, July 11, 2010 11:22 PM

Answers

  • Hi, As this thread has been quiet for a while, we assume that the issue has been resolved. At this time, we will mark it as "Answered". Either the previous steps should be helpful for many similar scenarios and will be marked as answer, or this post will be marked as answer in order to close the thread. Feel free to re-open the thread if you have additional information about this specific case or to open a new thread for a new case. In addition, we’d love to hear your feedback about the solution. By sharing your experience you can help other community members facing similar problems. Thanks,


    Bob Cornelissen - BICTT (My Blog about SCOM) - MVP 2012 and Microsoft Community Contributor 2011 + 2012 Recipient

    Monday, November 19, 2012 6:57 AM

All replies

  • More information from SQL Profiler

     

    exec dbo.TransformDeviceInstalledSoftwareItemFactProc @WaterMark=N'<Module ModuleName="TransformDeviceInstalledSoftwareItemFact" ProcessName="Transform" BatchId="1038">

      <Entity WarehouseEntityName="ComputerDim" WarehouseEntityTypeName="Dimension" EntityGuid="69d3fdc6-9269-3e55-233d-47f6fab49e55" WaterMarkType="BatchId" WaterMark="1030" MaxWaterMark="1038" />

      <Entity WarehouseEntityName="EntityRelatesToEntityFact" WarehouseEntityTypeName="Fact" EntityGuid="05ff9d46-a4a6-9cf5-b496-7e7602dd6fd0" WaterMarkType="BatchId" WaterMark="1030" MaxWaterMark="1038" />

      <Entity WarehouseEntityName="MTV_System$DeviceHasSoftwareItemInstalled" WarehouseEntityTypeName="Inbound" EntityGuid="fdf0de5b-5277-6f06-5098-79e289a52b5d" WaterMarkType="DateTime" WaterMark="Jun 27 2010 12:49:23:837PM" MaxWaterMark="Jul 11 2010 11:36PM" />

      <Entity WarehouseEntityName="SoftwareItemDim" WarehouseEntityTypeName="Dimension" EntityGuid="0e832a28-b13f-ed2c-10f4-1e36c5124910" WaterMarkType="BatchId" WaterMark="1030" MaxWaterMark="1038" />

    </Module>'

     

     

     

            INSERT INTO dbo.DataSourceDim(

                BaseManagedEntityId,

                SourceId,

                DataSourceName,

                SecureReferenceId,

                DisplayName,

                DateRegistered,

                DateUnRegistered,

                DatasourceType,

                ManagementGroupId,

                ProductVersion,

                RegistrationStatus,

                IsDeleted,

                InsertedBatchId,

                UpdatedBatchId

            )

            SELECT DISTINCT

                src.DataSourceId AS BaseManagedEntityId,

                src.DataSourceId AS SourceId,

                NULL AS DataSourceName,

                NULL AS SecureReferenceId,

                NULL AS DisplayName,

                NULL AS DateRegistered,

                NULL AS DateUnRegistered,

                NULL AS DatasourceType,

                NULL AS ManagementGroupId,

                NULL AS ProductVersion,

                NULL AS RegistrationStatus,

                0 AS IsDeleted,

                1038 AS InsertedBatchId,

                0 AS UpdatedBatchId

            FROM inbound.MTV_System$DeviceHasSoftwareItemInstalled src

            LEFT JOIN dbo.DatasourceDim dimDS ON

                src.DataSourceId = dimDS.SourceId

            WHERE src.DWTimeStamp > '2010-06-27T12:49:23.837'

                AND src.DWTimeStamp <= '2010-07-12T01:18:44.813'

                AND dimDS.SourceId IS NULL

                AND src.DataSourceId IS NOT NULL

           

    Warning: Null value is eliminated by an aggregate or other SET operation.

    Msg 50000, Level 14, State 1, Procedure TransformDeviceInstalledSoftwareItemFactProc, Line 335

    ErrorNumber="2627" Message="Violation of PRIMARY KEY constraint 'PK_DeviceInstalledSoftwareItemFact'. Cannot insert duplicate key in object 'dbo.DeviceInstalledSoftwareItemFact_2010_Jun'." Severity="14" State="1" ProcedureName="TransformDeviceInstalledSoftwareItemFactProc" LineNumber="243" Task="Step 9: Inserting into destination Fact"

    Monday, July 12, 2010 1:34 AM
  • Hi, As this thread has been quiet for a while, we assume that the issue has been resolved. At this time, we will mark it as "Answered". Either the previous steps should be helpful for many similar scenarios and will be marked as answer, or this post will be marked as answer in order to close the thread. Feel free to re-open the thread if you have additional information about this specific case or to open a new thread for a new case. In addition, we’d love to hear your feedback about the solution. By sharing your experience you can help other community members facing similar problems. Thanks,


    Bob Cornelissen - BICTT (My Blog about SCOM) - MVP 2012 and Microsoft Community Contributor 2011 + 2012 Recipient

    Monday, November 19, 2012 6:57 AM