SSIS Interview Questions: Incremental Loads, Error Outputs, and Package Recovery

A restartable package needs a durable load boundary; rerunning a failed task is not itself a recovery design. This guide works through the mechanism, a concrete example, and a verification path. These are original teaching scenarios, not claims that a named employer asks these exact questions.

Key takeaways

• Define the data or runtime boundary before choosing a setting.
• Make intermediate state observable and check it against a known expectation.
• Plan for retries, partial failure, and the cost of replay.
• Verify the exact product version and configuration in scope.

How does the core mechanism work?

Separate extraction progress from target commit. Record the source boundary, stage rows with a load identifier, validate counts and keys, then merge or commit the batch. Define which data-flow errors are quarantined and which fail the package.

In an interview, draw the data or control path and label each transition. State where the system persists state, where it can reject or retry work, and what evidence proves the transition completed. This is more useful than listing interface terms without connecting them to behavior.

Can you walk through a realistic example?

For a modified_at watermark at midnight, reread a small overlap window on the next run, deduplicate by business key and source version in staging, and advance the watermark only after the target write succeeds. If the package stops after staging, the next run can safely recognize that load rather than append duplicates.

Use a small example with numbers or explicit states. Trace the expected output by hand, then compare it with what the system reports. If the result differs, separate input problems, configuration problems, and runtime or downstream behavior before changing several variables at once.

What mistake should you avoid?

Redirecting a bad row to an error output does not handle it. Preserve the business key, original value, component, error code, and load ID. Checkpointing can help resume control flow, but it cannot guarantee that a partially written destination is safe to repeat.

Check	Why it matters
Inject a conversion error and inspect the quarantine row	It catches a wrong assumption early.
Stop after staging and rerun the same load	It preserves evidence for diagnosis.
Verify target keys prevent duplicate effects	It limits risk during recovery.
Advance watermark only after commit	It confirms the behavior after the change.

How would you debug it under interview pressure?

1. Inject a conversion error and inspect the quarantine row.
2. Stop after staging and rerun the same load.
3. Verify target keys prevent duplicate effects.
4. Advance watermark only after commit.
5. State the expected result and the rollback or recovery condition.

When the first fix does not work, return to the contract and collect one new piece of discriminating evidence. Avoid changing a timeout, threshold, index, exception, or permission merely because it is the easiest knob to find. Explain what trade-off the change introduces and how you would know whether it helped.

What should a strong answer include?

Summarize the invariant, show one worked case, name a failure boundary, and describe how you would validate the outcome. If behavior depends on a version or deployment setting, say so and identify the official reference you would check. Keep the distinction clear between what documentation guarantees and what you infer from the scenario.

SSIS Interview Questions questions become manageable when you connect the vocabulary to state, evidence, and recovery. Practice explaining the happy path once, then change a key assumption and see whether your answer still holds.

Sources and further reading

• Official documentation for SSIS Interview Questions

Research checked September 23, 2026. Verify version-specific behavior against the environment in the prompt.

SSIS: how would you make an incremental package restartable?

Assume a package extracts orders changed since a watermark. A fragile design advances the watermark before the destination commit; if the load fails, the next run skips records. A safer sequence reads a fixed high watermark, stages the bounded range, validates counts and keys, commits the destination, and only then records the completed watermark. The exact transaction boundary depends on the source and destination capabilities.

Design reruns to be idempotent: use a stable business key and an upsert or staging-and-swap pattern instead of blindly appending the same batch. Send rejected rows to an error output with the source key, failure reason, and batch identifier. Avoid storing sensitive row values in logs unless policy permits it.

In an interview, explain what happens if the process stops after each step. Recovery should answer three questions: which batch was attempted, whether its destination changes committed, and what a rerun will do. This is more convincing than saying “I would add a retry,” because retries alone do not define duplicate or partial-load behavior.