Bulk Importing Records

Last week I wrapped up a project that sounds simple on paper: take a spreadsheet and get it into Dataverse.

The spreadsheet has nearly a thousand historical training sessions, and each row is just a group code and the date that group was trained. In Dataverse, each session is a record (i.e., a row) in a table. The app already has a way to record a session - open the form, pick the group, click through the business process flow, validate. Doing that a thousand times by hand? No thanks.

This is a one-off, out of band backfill for historical sessions, not a new way of doing things. New sessions keep going through the app like normal. It’s a quick and dirty tool (well, as quick and dirty as something with three run modes gets).

So, insert a thousand rows. Easy! Except a training session in this app isn’t one row. Record one through the form and you end up with related attendance records, totals that add up, a validation on record, and a business process flow (BPF) marked “Finished”. Insert only the rows and you get a thousand sessions that look fine in a view and are broken everywhere else. The real job is leaving every session in the same state as if a person had clicked all the way through it, without double-running the app’s automation or making a half-failed run a nightmare to clean up.

Note: names, tables, choice values and IDs are swapped for generic ones (demo_ everything). The code is trimmed down from the real thing and leans on helpers I’m not showing, so it won’t compile on its own.

What Does “Finished” Even Mean?

The spreadsheet gives me two values. A “finished” session (a “completed flow”, in the business jargon) needs a lot more than that.

So I spent time sifting through the morass of dozens of tables, the BPF definitions, workflows - cloud (i.e., Power Automate) and classic - table metadata, and all the relevant dependencies. On a normal save, the form resolves the group’s reporting period and business unit, generates a name, creates attendance records for every active member, and maintains some flags and misc. totals. The BPF stage event handlers add more checks on top (e.g., OnPreStageChange and OnStageChange).

The catch: an SDK create doesn’t run any of the browser-side form scripts, but server-side validation and registered automation still run. In other words, some cloud flows trigger off Dataverse events tied directly to the BPF I’m trying to bulk import. So the importer has to redo what the form does on the client, while accounting for everything that still fires on the server.

A few questions also needed actual decisions before I wrote anything, because code can’t answer them:

• Who attended? Every currently active member of the group. This is a policy call - a two column spreadsheet can’t tell you who was in the room at a historical training. Real historical attendance would need a different source.
• Which reporting period? The current one for the group’s region.
• Training date? The spreadsheet date, as-is. The validation date is the UTC date the importer completes validation.
• Documents? None, even though the normal UX does in fact require them. The backfill skips that requirement and still goes all the way to complete.
• What counts as finished? Correct session and attendance data, updated group totals, and exactly one “Finished” BPF at the Summary stage. This is the list everything gets verified against at the end.

The importer is a .NET console app, with ClosedXML for the workbook and the Dataverse SDK for everything else. The core of it:

CreateNewParents(plans, metadata, options);         // the session rows
CreateOrUpdateAttendance(plans, metadata, options); // one row per member
EnsureBpfAtDocumentStage(plans, metadata, options); // park the BPF at documents
FinalizeParentTotals(plans, metadata, options);     // flags + misc. totals
CompleteSessions(plans, metadata);                  // one transaction each
VerifyCommittedPlans(plans, metadata, options);     // read it all back

each phase sets up the next one. Creating the session is just step one of six.

Dry Run by Default

The tool has three modes: validate the workbook offline, run a live preflight that writes nothing, or commit, which you have to ask for. This is probably where I “overdid it”, or as I like to call it, put in some extra effort. dry run is the default, because I didn’t want “forgot a flag” to turn into a thousand records.

Workbook validation catches bad headers, dates, empty values and duplicate group codes¹ (trimmed, case-insensitive, with the Excel row number kept for the report). The live preflight then resolves everything each row needs: exactly one active matching group, a business unit, a current reporting period, active members, compatible existing records, even generated name lengths against the live column limits. Nothing gets written until every row has a clean plan:

var plans = BuildPreflightPlans(rows, metadata, options);
WritePreflightReport(plans);

// one bad row stops the whole run, before a single write
if (plans.Any(plan => plan.Errors.Count > 0))
    return PreflightFailed;

// no commit flag, no writes
if (!options.Commit)
    return DryRunSucceeded;

Commit(plans, metadata, options);

It’s basically atomicity for the decision: either the whole workbook goes, or none of it does.

This paid off right away in the sandbox. One 100-row test file had 20 group codes that didn’t match an active group.² Preflight reported all 20 and changed nothing, not even the 80 good rows. I made a clean workbook from those 80 and went again.

Note: the writes themselves are not atomic, though. Once commit starts, writes succeed or fail individually, so the workbook as a whole isn’t one big transaction. The all-or-nothing part comes later, per session.

Some annoying detail here: the repo’s solution export didn’t match the target environment. Its training type choices were different from the published ones, and an early metadata check caught it. So the importer resolves the choice from published metadata on both tables, by label and expected value, and fails on anything unexpected instead of guessing. Same for the BPF table, status values and column limits.

What it can’t fully check is permissions. The account running it needs the right table privileges, including the BPF table, plus field security access for a secured validation column. Being able to log in tells you nothing about either. Ask me how I know :)

Doing the Form’s Job

Each new session gets one attendance row per member, with the session and participant lookups, training type and date, attended/closed flags, and the group’s business unit. nothing exotic. The relationships matter as much as the flags though: totals without attendance rows break the detail views, and attendance rows without the right totals leave the session inconsistent.

A few gotchas:

Paging. Getting the members has to get all of them. The query follows MoreRecords and the paging cookie (5,000 a page) and splits big ID filters into chunks of 200. Otherwise a truncated query becomes an attendance list, and a total, that looks plausible and is wrong.

Dates. The historical date stays a DateOnly until the SDK boundary:

static DateTime ToDataverseDate(DateOnly date)
{
    var midnight = date.ToDateTime(TimeOnly.MinValue);

    // unspecified, so nothing shifts it into some other timezone
    return DateTime.SpecifyKind(midnight, DateTimeKind.Unspecified);
}

i.e., a calendar date isn’t a UTC instant. This fits these columns’ date behavior, not every Dataverse date/time column.

Owner vs. creator. Assigning an owner doesn’t change createdby, and the BPF shows the creator as the requester. So a different owner doesn’t change who the BPF says requested it.³

Making It Safe to Run Twice

A bulk job can die halfway through, or the server accepts a write and the response never comes back. If every restart just creates new sessions, you get duplicates. So before creating anything, the importer looks for a session that already exists:

• same group, same training type, same spreadsheet date, still active: reuse it, along with the reporting period it was saved with
• more than one match: that’s an error, stop and look
• it searches across reporting periods on purpose, so a retry still finds the original session if the region’s current period changed in between

Open and completed sessions get different rules:

• open session: missing attendance rows get created and expected ones updated. anything weird (duplicate participants, unexpected rows, closed children) fails the plan instead of getting quietly “fixed”.
• completed session: if everything checks out, it’s a successful no-op. Attendance does not get rebuilt from today’s membership, and the process doesn’t move backwards.

Current membership is the input for a new session; saved attendance is the record of a completed one.

There’s no local journal to replay, either. a killed process might never write its results file, so the next run just checks Dataverse again.

The BPF Is Its Own Record

Something I had to train myself on right away when I took this project on: the session’s status and the BPF instance’s state are separate things. Closing the session doesn’t make the process “Finished”.

For new sessions, I don’t let Dataverse auto-pick a process when the session gets created. The importer makes the BPF record itself, already sitting at the documents stage, with the earlier stages filled in like someone clicked through them:

// the BPF instance is its own row, pointing back at the session
var process = new Entity("demo_trainingprocess", Guid.NewGuid());
process["bpf_demo_trainingsessionid"] =
    new EntityReference("demo_trainingsession", parentId);

// start it at the documents stage...
process["activestageid"] =
    new EntityReference("processstage", stages.Documents);

// ...like someone already clicked through the first two
process["traversedpath"] = string.Join(",",
    stages.SelectGroup,
    stages.SelectAttendees,
    stages.Documents);

An existing BPF at an earlier stage gets moved forward one stage at a time instead. Those are two different operations, and Microsoft documents both in Business process flows using code.

Fun fact: this process has two stages named “Validation”.⁴ So names alone are useless, and the importer checks the known stage IDs from the solution along with their names. 🤔

One Transaction per Session

The prep writes go out with ExecuteMultiple in batches of 50, with ContinueOnError on and every response checked against its Excel row. Fewer round trips, and one bad row doesn’t hold up the rest. But it’s not atomic: each request runs in its own database transaction (docs).

Completion has to be atomic, since a session should never be closed while its attendance and BPF aren’t. So each session gets an ordered set of updates:

1. Move the BPF to Validation.
2. Set the session’s validation action, validation date, closed flag and Closed status reason, all together.
3. Close every attendance row.
4. Update the group’s trained count and reporting period.
5. Move the BPF to Summary.
6. Mark the BPF “Finished”.

Sent as one ExecuteTransaction:

var transaction = new ExecuteTransactionRequest
{
    ReturnResponses = false,
    Requests = new OrganizationRequestCollection()
};

foreach (var request in completionRequests)
    transaction.Requests.Add(request);

// all six steps land together, or none of them do
service.Execute(transaction);

If anything fails, the transaction rolls back and the session sits at a recognizable “prepped, not completed” state. Earlier prep writes stay. One transaction per session, not one for the whole workbook.

Also, transaction size != batch size. Completion is one update per attendance row plus five, and preflight checks that against a 1000 request limit. A smaller ExecuteMultiple batch won’t make it fit.

Dodging a Cloud Flow (Politely)

My (least?) favorite part.

An existing cloud flow watches the session’s validation action column. When the action changes to Validate, the flow closes out the session and all its attendance, but only if the session isn’t already marked closed. If I set the action and waited for the flow, completion would happen asynchronously, outside my transaction. If I also closed everything myself, the flow would just do the same work a second time. So I use the flow’s own condition against it:

// all in one update. closed is already true, so the flow has nothing left to do
var parent = new Entity("demo_trainingsession", parentId);
parent["demo_validationaction"] = new OptionSetValue(values.Validate);
parent["demo_validationdate"] = completionTimeUtc.Date;
parent["demo_closed"] = true;
parent["statuscode"] = new OptionSetValue(values.ParentClosed);

completionRequests.Add(new UpdateRequest { Target = parent });

The flow still triggers, but the session’s already closed, so it skips the closing-out entirely. The importer does the closing itself, inside the transaction. Bonus: it also works in a sandbox where the flow is turned off.

Order matters for one more reason. Entering Summary runs a synchronous guard that rejects unvalidated sessions, so Validate has to be set before the BPF moves to Summary. And watch state vs. status: the session’s Closed status reason lives under the Active state, while the BPF’s “Finished” status lives under Inactive. “Just deactivate it” is wrong for one of them.

None of this turns automation off in general. A separate visit-maintenance flow still runs asynchronously on session writes. And with Dataverse row-change triggers, including a selected column in an update can fire a flow even if the value didn’t change. Keep update payloads small, and read the actual trigger conditions instead of assuming SDK writes skip flows. They don’t, in this case.

Trust, but Verify!

A successful response means a request was accepted. That’s it. A session only counts as Completed after the importer reads back the session, its attendance, the BPF (exactly one, “Finished”, at Summary, expected path) and the group totals - i.e., once it verifies the criteria I laid out at the beginning of this post.

Counting attendance rows also just isn’t enough. Two rows for one person and zero for another still gives the right count, so it checks for exactly one row per planned participant too:

// bucket what we read back by participant
var byParticipant = attendance
    .GroupBy(row => row.ParticipantId)
    .ToDictionary(group => group.Key, group => group.ToList());

// right total, AND exactly one row for everyone we planned for
bool membershipMatches = attendance.Count == plannedMembers.Count
    && plannedMembers.All(member =>
        byParticipant.TryGetValue(member.Id, out var matches)
        && matches.Count == 1);

a failed check doesn’t undo anything. It flags the row in the report for a human. And it’s meant to run one at a time: a local file lock stops overlapping commits from the same output directory, but there’s no alternate key or row-version check on the server side.

So What?

I ran a clean batch of 80 in the sandbox first. All 80 went through, which works out to over 3,000 attendance records, and every one of them checked out when the importer read it back. Took about seven and a half minutes. Then the real thing: the full batch, 900 and some sessions, in production. Nothing broke!

The completion phase did give me a small scare. It prints a phase message and then just… grinds through transactions one at a time with no progress output. It looks exactly like a hang. If I ever had to run it again, I’d add a progress line per session first.

There are nine unit tests, covering workbook and command line handling, some schema constants, and what the completion builder produces. They don’t cover the full orchestration, a real rollback or every recovery path - that’s what the sandbox run was for.

In short, the hardest part was actually fully defining what “completed” means for a given session “event” (more business jargon I told myself I wouldn’t introduce, but oh well). After that the rest kind of fell out of it: validate everything before writing, reconcile before creating, leave completed attendance alone, one transaction per session, and read it back before calling it done.