How to – Delete the elastic table records (Dataverse)

Recently we wrote an SSIS package to delete the Elastic Table records using KingswaySoft’s Integration Toolkit for Dynamics 365.

We had mapped the primary field for deletion and the package was also showing success.

However back in our CRM / Sales Hub app, we saw that none of the records were deleted (total – 48999)

The reason it was showing success is that we had specified the Ignore Error option in our CDS Destination component.

Then we created 2 more records but didn’t specify the partition ID for them.

This time on the successful run of the package we can see those 2 new records getting deleted for which we didn’t specify any partition ID i.e. Test 1 and Test 2 records were deleted successfully.

If we check the Microsoft docs it mentions that we need to include the partition ID using the alternate key to delete those records using the DeleteMultiple request.

Could not find a way to specify an Alternate Key in the CDS Destination component for the Delete message and if we try deleting the records one by one instead of using the DeleteMultiple request we get the below error.

[CDS Destination [2]] Error: An error occurred with the following error message: “System.Exception: Error(s) occurred when processing the batch: [1] KingswaySoft.IntegrationToolkit.DynamicsCrm.WebAPI.WebApiServiceException: The remote server returned an error: (404) Not Found. (Error Type / Reason: NotFound, Detailed Message: {“error”:{“code”:”0x80040217″,”message”:”The HTTP status code of the response was not expected (404).\n\nStatus: 404\nResponse: \n{\”error\”:{\”message\”:\”Could not find item ‘b3a70971-9674-ef11-a671-6045bdfe58ee’.\”,\”details\”:[{\”message\”:\”\\r\\nErrors : [\\r\\n \\\”Resource Not Found. Learn more: https://aka.ms/cosmosdb-tsg-not-found\\\”\\r\\n]\\r\\n\”}]}}”}}) (SSIS Integration Toolkit for Microsoft Dynamics 365, v23.2.2.32701 – DtsDebugHost, v16.0.5270.0)System.Net.WebException

As expected, using CrmServiceClient also if we do not include partitionid we will get the below error for the records that have partition id specified.

The HTTP status code of the response was not expected (404).

Response:

{“error”:{“message”:”Could not find item ‘b3a70971-9674-ef11-a671-6045bdfe58ee’.”,”details”:[{“message”:”\r\nErrors : [\r\n \”Resource Not Found. Learn more: https://aka.ms/cosmosdb-tsg-not-found\”\r\n]\r\n”}]}}

Here we can specify the partitionId parameter to delete those records having the partitionId specified in the DeleteRequest

For DeleteMultiple Request we need to provide the alternate key as shown below.

We will have the alternate key auto-created by the system when we create an elastic table.

Sample Code –

 var myServiceClient = new CrmServiceClient(connectionString);
        var query = new QueryExpression("custom_myelastictable");
        query.ColumnSet.AddColumns("custom_name", "partitionid");
        var myElasticTableCollection = myServiceClient.RetrieveMultiple(query);
        var lstEntityRefCollection = new EntityReferenceCollection();

        // Delete Request
        foreach (var elasticTable in myElasticTableCollection.Entities)
        {
            var deleteRequest = new DeleteRequest();
            deleteRequest.Target = new EntityReference("custom_myelastictable", elasticTable.Id);
            deleteRequest.Parameters["partitionId"] = elasticTable.Attributes["partitionid"];
            var response = myServiceClient.Execute(deleteRequest);
        }

        // DeleteMultiple Request 
       
        foreach (var elasticTable in myElasticTableCollection.Entities)
        {
            var entityRef = new EntityReference("custom_myelastictable", elasticTable.Id);
            entityRef.KeyAttributes.Add("custom_myelastictableid", elasticTable.Id);
            entityRef.KeyAttributes.Add("partitionid", elasticTable.Attributes["partitionid"]);
            lstEntityRefCollection.Add(entityRef);
        }

        var deleteMultipleRequest = new OrganizationRequest();
        deleteMultipleRequest.RequestName = "DeleteMultiple";
        deleteMultipleRequest.Parameters.Add("Targets", lstEntityRefCollection);
        myServiceClient.Execute(deleteMultipleRequest);

Hope it helps..

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology ! View all posts by Nishant Rana