One Failure, Two Recoveries

We have written before about the restart-and-recovery state machine your BimlCatalog already owns: a failed batch resumes from the failure point instead of restarting from zero, and the memory that makes that possible lives in a database you deploy and control. That post covered the state machine's existence. This one goes a level down, into a branch that post never opened: what the recovery flag actually triggers on the next run, and why the answer is different on SSIS than on Azure Data Factory.

‍

Because here is the part that surprises people the first time they trace it. The catalog records the failure the same way on both platforms. Same table, same column, same one-character flag. But the next execution does two different things with it. On SSIS, a rollback container fires and rewinds the partial load before reloading. On ADF, the package simply runs again. Neither behavior is an accident, and the difference tells you something about how the whole orchestration layer is designed.

‍

One flag at failure

‍

When a BimlFlex load fails, the error-logging procedure in your BimlCatalog does two things to the execution record: it marks the run Failed, and it sets a column called NextLoadStatus to R. That column is not a status of the run that just died. It is an instruction to the run that has not happened yet.

‍

You can watch this in your own database, because the BimlCatalog ships as a DACPAC you deploy yourself and the bfx schema is yours to query:

‍

SELECT ExecutionID, ExecutionStatus, NextLoadStatus, StartTime, EndTime 

FROM bfx.Execution 

WHERE PackageID = @PackageID 

ORDER BY ExecutionID DESC; 

‍

After a failure you will see the dead run sitting there with ExecutionStatus = 'F' and NextLoadStatus = 'R'. If the failure happened inside a batch, the siblings that finished cleanly get NextLoadStatus = 'C', which means skip: the next batch run will not repeat work that already succeeded. That skip behavior is the resume-not-restart story from the earlier post. The R is where this post lives.

‍

Note what the flag does not say. It does not say "truncate the staging table." It does not say "delete satellite rows." It says, in effect, the previous run failed and the next run needs to recover. The catalog records intent and leaves the method to the platform. That restraint is what makes the rest of this work.

‍

SSIS rolls back first

‍

On SSIS, the next execution begins the way every BimlFlex execution begins: the package calls the start-logging procedure in the catalog, which reads the previous run's NextLoadStatus. Finding R, it starts the new execution in a dedicated rollback status, and that status flows back into the package as a variable.

‍

Every generated SSIS package in an orchestrated project carries a sequence container for exactly this moment. Open one in Visual Studio and you will find it: SEQC - BimlFlex Rollback Previous Execution, connected to the start-logging task by a precedence constraint whose expression is @ExecutionStatus == "R". On a normal run the expression is false and the container never executes. You could run a package nightly for a year and never see it fire. It is dormant recovery machinery, generated into every package because the one night you need it is not a night you want to be writing cleanup SQL by hand.

‍

When it does fire, the container runs Execute SQL tasks that return each target to the state of the last successful execution. The catalog supplies the boundary: the start-logging procedure returns the ID of the last run that actually succeeded, and every rollback statement is parameterized on it. Staging tables are truncated outright. Persistent staging and Data Vault targets are trimmed by audit id, with statements of this shape:

‍

DELETE FROM [rawvault].[sat_Customer] 

WHERE [FlexRowAuditId] > ? 

‍

That FlexRowAuditId column is the row-level audit stamp BimlFlex writes on every load, the same one that powers lineage queries back into the catalog. Here it earns its keep a second way: any row with an audit id greater than the last successful execution's ID was written by a failed run, so it goes. Satellites get one more touch, restoring end-dating on current rows the failed run had closed off, so the timeline is intact before the reload begins. Rollback done, the same run proceeds to load. One execution, rewind then redo.

‍

Two honest qualifications. Rollback is opt-in per layer, controlled by settings you can see in the product: EnableRollbackStg for staging, EnableRollbackPsa for persistent staging, EnableRollbackDv for Data Vault targets. And it is an SSIS behavior, which brings us to ADF.

‍

ADF re-executes

‍

On Azure Data Factory, the generated pipeline calls the same start-logging procedure at the same point, and the procedure reads the same R flag. Then it does something different: it starts the new execution as a plain execute. No rollback status, no rewind. The failed package just runs again.

‍

There is no rollback container in a generated ADF pipeline because the generated ADF pattern does not implement rollback mode. Recovery on ADF leans on the two things the platform story already provides. First, the batch-level skip: everything that succeeded before the failure is flagged C and will not re-run, so a re-execute only repeats the failed leg, not the batch. If you have read our piece on automating change data capture on ADF, you have seen this restart discipline before, applied to CDC windows. Second, the generated load patterns themselves are built to be re-runnable, so executing the failed leg again converges on the same result instead of doubling it.

‍

The operational consequence is worth stating plainly: on ADF, recovery is usually just "run it again." The catalog has already fenced off the work that succeeded. The re-execute picks up the one piece that did not.

‍

Why they diverge

‍

It would have been simpler to make the flag prescriptive. Write "rollback" into the catalog at failure time and have every platform obey. But then the flag would be an SSIS instruction stored in a platform-neutral table, and an ADF run reading it would be stuck honoring semantics its runtime was never given. The other easy option is worse: level down, make every platform do plain re-execution, and throw away the stronger recovery SSIS can actually deliver.

‍

The design that ships does neither. The catalog records the platform-neutral fact, recovery needed, and each code generator emits the strongest recovery its runtime supports. SSIS packages get the full rewind because a package can host conditional cleanup wired to an expression. ADF pipelines get fenced re-execution because that is the reliable pattern there. The operator's view stays unified: one schema, one query, one mental model of P, C, and R, whatever mix of platforms is running underneath. We walked through how generated pipelines talk to the catalog in the dev diary on BimlCatalog integration; this is that conversation carrying real weight.

‍

Who owns the recovery logic

‍

Notice where all of this lives. The flag is a column in a table in your database. The decision logic is stored procedures in that same database, deployed from a DACPAC, readable with any SQL client. The SSIS half of the behavior is in packages you generated and own. The ADF half is in pipelines you generated and own. The BimlCatalog observes and advises; it is not sitting on the execution path, and nothing about recovery requires a Varigence-hosted component to be awake at 2 a.m.

‍

That is not a given in this category. WhereScape's own technical architecture white paper is candid that audit, restart, and lineage depend on the bundled RED Scheduler: run the generated code outside it and the restart logic goes with it. The scheduler is the orchestrator, and it is theirs. BimlFlex's equivalent machinery is a database you could query, extend, and monitor yourself, which is also what makes the whole thing observable: the same bfx.Execution rows that drive recovery are the ones you can alert on, as we covered in runtime checks, logs, and triage.

‍

So the next time a load dies overnight, the question to ask your catalog is not "what broke." It is "what will the next run do." Query bfx.Execution, find the R, and you already know the answer: on SSIS, rewind then reload; on ADF, re-execute what failed and skip what did not. Same flag, two recoveries, each the strongest one its runtime can make.