Why Did My Nightly Load Skip and Still Report Success?
Stale Restart Skip Flags And The Setting That Expires Them
October 5, 2026
Your nightly load most likely skipped because an earlier batch failed and nobody reran it. When a batch fails, the BimlCatalog marks every package that already finished inside it to be skipped on the next run, so a prompt restart doesn't load the same data twice. That flag has no clock on it. If the next start is tonight's scheduled run and not a restart, the flagged packages still skip, finish in a second or two, and report Success. The v26.2.206 BimlCatalog adds a setting for this, RestartFlagExpiryHours, used by both SSIS and Azure Data Factory orchestration. At 0, the default, flags never expire, which is the behavior you have today. Set it to a positive number of hours and any skip flag older than that is ignored, so the package runs normally.
We've already covered how the BimlCatalog restart state machine resumes a failed load instead of starting from zero. That post is about the restart you want. This one is about the opposite case: a skip that's still sitting there after the moment for a restart has passed.
How a Failed Batch Leaves Flags
Every execution in your BimlCatalog is a row in bfx.Execution, and two single-character columns on that row decide what happens next. ExecutionStatus says what this run did. NextLoadStatus tells the next run of the same package what to do. Your catalog's own rpt.ExecutionDetails view spells the codes out:
ExecutionStatus: E: Executing
ExecutionStatus: S: Success
ExecutionStatus: F: Failed
ExecutionStatus: C: Skipped
NextLoadStatus: P: Process
NextLoadStatus: C: Cancel
NextLoadStatus: R: Rollback
A batch row has a ParentExecutionID of -1. Each package run inside it points at the batch through that same column.
When a package finishes normally, its row closes as S / P: success, process normally next time. When the batch fails, the catalog walks the batch's children. Each child that already succeeded gets its NextLoadStatus changed to C. The failed child and the batch itself close as F / R. Those packages already did their work for this cycle, so a restart should step over them and redo only what broke.
Why the Next Run Skips
Each time a package starts, the catalog reads the most recent row for that package and acts on its NextLoadStatus. It doesn't ask whether the start is a restart or a schedule. It doesn't know. A C means skip.
Here's how that plays out on a nightly batch. It starts at 01:00. Your customer and product loads finish by 01:40. The order load fails at 03:10, and the batch fails with it. It's a busy morning and nobody reruns the batch. At 01:00 the next night, the scheduler starts it as usual:
- The customer and product loads read
C, skip, and close as Success in about a second. - The order load reads
Rand runs again (a retry on Data Factory, rollback handling on SSIS). - The batch finishes green.
The skip consumes the flag, so the night after that everything runs normally. But for one cycle, two loads did nothing and reported success. Whether you actually lose data depends on the load. An incremental load that tracks its own high-water mark picks up two days of changes the next night. A load that takes a dated snapshot has a missing day.
The generated orchestration treats a skip the same way on both engines. On SSIS, the package sees the skip status and goes straight to its end step. On Data Factory, the main activity's condition is false and the pipeline goes on to log the end. Either way the catalog row closes as Success, and so does the run in your orchestrator's monitoring.
One other thing produces a skip: a package disabled in the catalog. That one is deliberate. It closes with NextLoadStatus P, so it never leaves a flag behind.
Finding Skips and Stale Flags
To check whether this happened, look for runs whose previous row for the same package carried a skip flag. This works because a flag set by a failed batch stays on that old row. Nothing resets it later.
WITH Runs AS (
SELECT e.[ExecutionID],
e.[PackageID],
e.[ParentExecutionID],
e.[ExecutionStatus],
e.[Duration],
CAST(e.[StartTime] AS DATETIME2(7)) AS [StartedAt],
LAG(e.[NextLoadStatus]) OVER (PARTITION BY e.[PackageID] ORDER BY e.[ExecutionID]) AS [PrevNextLoadStatus],
LAG(e.[ExecutionStatus]) OVER (PARTITION BY e.[PackageID] ORDER BY e.[ExecutionID]) AS [PrevExecutionStatus],
LAG(CAST(COALESCE(e.[EndTime], e.[StartTime]) AS DATETIME2(7)))
OVER (PARTITION BY e.[PackageID] ORDER BY e.[ExecutionID]) AS [PrevEndedAt]
FROM [bfx].[Execution] e
)
SELECT p.[ProjectName],
p.[PackageName],
r.[ExecutionID],
r.[StartedAt],
r.[Duration] AS [DurationSeconds],
DATEDIFF(MINUTE, r.[PrevEndedAt], r.[StartedAt]) / 60.0 AS [FlagAgeHours]
FROM Runs r
JOIN [bfx].[Package] p ON p.[PackageID] = r.[PackageID]
WHERE r.[ParentExecutionID] <> -1
AND r.[PrevNextLoadStatus] = 'C'
AND r.[PrevExecutionStatus] IN ('S', 'C')
AND r.[ExecutionStatus] IN ('S', 'C')
AND r.[StartedAt] >= DATEADD(DAY, -7, GETDATE())
ORDER BY r.[StartedAt] DESC;
Read the last two columns together. A duration near zero means the package skipped. A small FlagAgeHours is a restart doing its job. A flag age close to your schedule interval is the silent skip this post is about.
To see flags that are waiting right now, before tonight's run reads them, check the most recent row per package. If your catalog's UseGETUTCDATE setting is Y, swap GETDATE() for GETUTCDATE() so the ages line up with how the catalog records time.
DECLARE @Now DATETIME2(7) = GETDATE();
WITH LastRun AS (
SELECT e.[ExecutionID],
e.[PackageID],
e.[ParentExecutionID],
e.[ExecutionStatus],
e.[NextLoadStatus],
CAST(COALESCE(e.[EndTime], e.[StartTime]) AS DATETIME2(7)) AS [FlaggedAt],
ROW_NUMBER() OVER (PARTITION BY e.[PackageID] ORDER BY e.[ExecutionID] DESC) AS [rn]
FROM [bfx].[Execution] e
)
SELECT p.[ProjectName],
p.[PackageName],
lr.[ExecutionID],
lr.[FlaggedAt],
DATEDIFF(MINUTE, lr.[FlaggedAt], @Now) / 60.0 AS [FlagAgeHours],
bp.[PackageName] AS [BatchName],
b.[ExecutionStatus] AS [BatchStatus]
FROM LastRun lr
JOIN [bfx].[Package] p ON p.[PackageID] = lr.[PackageID]
JOIN [bfx].[Execution] b ON b.[ExecutionID] = lr.[ParentExecutionID]
JOIN [bfx].[Package] bp ON bp.[PackageID] = b.[PackageID]
WHERE lr.[rn] = 1
AND lr.[ParentExecutionID] <> -1
AND lr.[NextLoadStatus] = 'C'
AND lr.[ExecutionStatus] IN ('S', 'C')
ORDER BY [FlagAgeHours] DESC;
The ExecutionStatus filter matters. A package that's running right now also carries NextLoadStatus C until it finishes, and you don't want those in the list. Every row this returns will skip on its package's next start unless an expiry window says otherwise.
If this is part of your runtime checks and triage, run the second query after your batch window closes. Any row older than a few hours is a failed batch someone meant to rerun and didn't.
Setting RestartFlagExpiryHours
The setting is a row in admin.Configurations. Deploying the v26.2.206 catalog adds the key with a value of 0 if it isn't already there. A redeploy doesn't overwrite a value you've changed.
UPDATE [admin].[Configurations]
SET [ConfigurationValue] = '16'
WHERE [ConfigurationCode] = 'BimlFlex'
AND [ConfigurationKey] = 'RestartFlagExpiryHours';
Here's what the values do:
0, a negative number, or anything that isn't a whole number: flags never expire.- A positive number: when a package starts and its flag is older than that many hours, the catalog ignores the flag and the package runs normally.
- Anything above
87600(ten years) is treated as87600. A value pasted in the wrong unit, say milliseconds, would otherwise overflow the date math and make every start of the package fail, real restarts included.
The catalog reads the setting each time a package starts, so a change applies to the next start. There's nothing to regenerate or redeploy.
The setting only touches C flags. A failed package's R still triggers its retry or rollback however old it is, so the load that actually broke always runs again.
Every expiry is logged. The catalog writes a row to bfx.ExecutionActivity on the new execution, whether or not you've turned on debug logging:
SELECT a.[ActivityDate], p.[PackageName], a.[ExecutionID], a.[Activity]
FROM [bfx].[ExecutionActivity] a
JOIN [bfx].[Package] p ON p.[PackageID] = a.[PackageID]
WHERE a.[Activity] LIKE N'Restart flags expired%'
ORDER BY a.[ActivityDate] DESC;
The message names the package, the old execution that set the flag, and when that execution ended, followed by executing normally. When an auditor asks why a package ran on a night after a failure, that row is your answer.
Choosing the Window
The clock starts when the flagged package finished in the failed run. It doesn't start when the batch failed. That detail sets both limits.
The floor is your restart window. A package can finish early in a long batch, so its flag is already old by the time the batch fails. The expiry has to cover your longest batch runtime plus the longest you'd realistically wait before a rerun. If it's shorter, a legitimate restart will rerun packages that already loaded.
The ceiling is your schedule interval. At the next scheduled start, the flag is about one interval old, minus how long the package itself ran, minus however much its start time moves from night to night. The expiry has to be shorter than that, or the silent skip still happens.
Here's an example with a nightly batch. Say it runs for up to four hours and your team reruns failures within eight hours. That puts the floor at about 12 hours. Say the longest package inside it runs two hours and start times move by up to an hour. That puts the ceiling at about 21 hours. Anything from 14 to 18 leaves room on both sides. That's where the 16 above came from.
Two situations don't fit neatly.
Frequent schedules. On an hourly batch, the floor is bigger than the ceiling. No value covers a rerun and still expires before the next run, because the next run effectively is your restart. Leave it at 0.
One value per catalog. RestartFlagExpiryHours applies to every package logged to that BimlCatalog. If one catalog serves both an hourly batch and a nightly one, the hourly batch sets the ceiling. Either rerun nightly failures within that window, or leave the setting at 0 and use the stale-flag query as your alert.
There's also a real trade-off. When a flag expires, a package that already succeeded runs again for the same cycle. For an insert-only Data Vault load that compares hashes before inserting, running again is harmless. For an append-only load with no duplicate check, it isn't. Know which of your loads are safe to repeat before you pick a number. We'd still set one for most nightly estates, because a duplicate load gets noticed and a quietly skipped one often doesn't.
Making Skips Visible on Data Factory
Expiry fixes the stale skip. It doesn't change the fact that a correct skip, during a real restart, also closes as Success. On Data Factory, the v26.2.206 catalog adds a second setting for that: PreserveSkippedExecutionStatus.
Set it to Y and a skipped run keeps ExecutionStatus C when it finishes, instead of being overwritten to S. It shows up as Skipped in rpt.ExecutionDetails. Its NextLoadStatus still resets to P, so the skip still uses up the flag. If a restart fails again, packages skipped during that restart get flagged again, the same as packages that succeeded, so a third attempt won't reload them. The default is N, which keeps skipped runs recorded as Success.
This setting is Data Factory only. SSIS skipped runs still close as Success. On SSIS, the queries above are how you see skips.
If your Data Factory pipelines call the catalog the way we described in orchestrating Mapping Data Flows, both settings apply without regenerating anything. The pipelines already call the catalog at start and end. The decision happens inside the catalog database, which you deploy and own. If you're newer to the catalog, the dev diary on the BimlCatalog control framework covers how each run gets registered in the first place.
A skip flag is a promise that this cycle's work is already done. Give it an expiry so it can't still be making that promise a day later.
