Dataverse: SSIS vs. ExecuteMultipleRequest

I am always curious to try SSIS (as mostly my experience is on customizing CRM CE/Dataverse side) as people are always telling me how fast it is to process large data. So today, we will learn and compare ExecuteMultipleRequest vs. SSIS. In the SSIS part, we will be using KingswaySoft for the SSIS Integration toolkit.

For the scenario, I have the Contact records (88606) that we will retrieve and then create a Demo record based on that data.

SSIS

The SSIS part is quite easy to build. We just need to create a Data Flow task. Then add Dynamics CRM Source where I set like below:

Next, I added Dynamics CRM Destination with this configuration:

For the Columns mapping part:

Once done, we can run it on your local machine, and here is the result:

As you can see, with a total of 88606 records, it needs 00:14:37.640.

ExecuteMultipleRequest

Next, we will test the best method that I can find to fight against the SSIS. We can combine retrieve paging + the best ExecuteMultipleRequest for large data from Mark Carrington (I read the implementation from his blog post that you can read here):

using Microsoft.Extensions.Configuration;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;
using System.Text;
using System.Xml;
var config = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();
var settings = config.GetRequiredSection("Settings").Get<AppSetting>();
var client = new ServiceClient(settings.DataverseConnectionString);
var fetchCount = 5000;
// Initialize the page number.
var pageNumber = 1;
// Initialize the number of records.
var recordCount = 0;
// Specify the current paging cookie. For retrieving the first page, 
// pagingCookie should be null.
string pagingCookie = null;
// Same with SSIS settings
const int maxRequestsPerBatch = 1000;
var fetchXml = @"<fetch xmlns:generator='MarkMpn.SQL4CDS'>
  <entity name='contact'>
    <attribute name='firstname' />
    <attribute name='lastname' />
    <order attribute='firstname' />
  </entity>
</fetch>";
var start = DateTime.Now;
var listTasks = new List<Task>();
while (true)
{
    var xml = CreateXml(fetchXml, pagingCookie, pageNumber, fetchCount);
    var service = client.Clone();
    var result = service.RetrieveMultiple(new FetchExpression(xml));
    Parallel.ForEach(result.Entities,
        new ParallelOptions { MaxDegreeOfParallelism = client.RecommendedDegreesOfParallelism },
        () => new
        {
            Service = client.Clone(),
            EMR = new ExecuteMultipleRequest
            {
                Requests = new OrganizationRequestCollection(),
                Settings = new ExecuteMultipleSettings
                {
                    ContinueOnError = false,
                    ReturnResponses = false
                }
            }
        },
        (entity, loopState, index, threadLocalState) =>
        {
            var lastName = entity.GetAttributeValue<string>("lastname");
            var firstName = entity.GetAttributeValue<string>("firstname");
            var contactRef = entity.ToEntityReference();
            var create = new Entity("tmy_demo")
            {
                ["tmy_contact"] = contactRef,
                ["tmy_filter"] = lastName,
                ["tmy_name"] = firstName,
            };
            threadLocalState.EMR.Requests.Add(new CreateRequest { Target = create });
            if (threadLocalState.EMR.Requests.Count != maxRequestsPerBatch) return threadLocalState;
            threadLocalState.Service.Execute(threadLocalState.EMR);
            threadLocalState.EMR.Requests.Clear();
            return threadLocalState;
        },
        (threadLocalState) =>
        {
            if (threadLocalState.EMR.Requests.Count > 0)
            {
                threadLocalState.Service.Execute(threadLocalState.EMR);
            }
            threadLocalState.Service.Dispose();
        });
    if (result.MoreRecords)
    {
        Console.WriteLine("\n****************\nPage number {0}\n****************", pageNumber);
        // Increment the page number to retrieve the next page.
        pageNumber++;
        // Set the paging cookie to the paging cookie returned from current results.                            
        pagingCookie = result.PagingCookie;
    }
    else
    {
        // If no more records in the result nodes, exit the loop.
        break;
    }
}
var end = DateTime.Now;
Console.WriteLine($@"Start: {start}. End: {end}. Total minutes: {(end - start).TotalMinutes}");
Console.ReadKey();

And for the result:

Summary

As you can see, ExecuteMultipleRequest also performs quite well compared to SSIS. But of course, you can see the required amount that you need to spend configuring SSIS and preparing the code. For me, SSIS + KingswaySoft required less amount effort (you also can implement multiplexing – execute batch using multiple accounts easily. And by default, the implementation already supports multi-threading) but required more licensing compared to Pro Dev.

Happy CRM-ing!