SSIS Interview Questions and Answers: Reliable Packages, Lookups and Recovery

TL;DR

• Prepare SSIS interview answers using a concrete package, realistic bad rows and a failure you can reproduce.
• Separate orchestration, row processing, connection configuration and destination correctness in your explanation.
• Checkpoints restart control-flow work; they do not resume halfway through a Data Flow task or guarantee duplicate-free business processing.
• Test lookup comparisons, rejected rows and repeat execution before describing a package as reliable.

Use a small package to explain your decisions

SQL Server Integration Services interview questions become easier when you can describe a complete example. Imagine receiving an orders file, validating its rows, finding customer keys and loading an order table. The input has missing customers, invalid amounts and duplicate event identifiers. A destination connection sometimes fails.

These are original SSIS practice questions, not claimed employer questions. Explain the design you would choose, then show what evidence would confirm it. When a feature depends on SQL Server version, deployment model or infrastructure, state the environment you have actually used.

The goal is to show that you can make a package understandable and recoverable. An interviewer should be able to follow one row from its source to its destination, including what happens when that row is rejected.

1. How do control flow and data flow differ?

Control flow coordinates tasks and containers. Data flow moves and transforms rows through sources, transformations and destinations. A Data Flow task participates in the broader control flow. Describe this distinction with the order of work rather than treating the two designers as interchangeable pictures. Microsoft's control flow overview explains the orchestration model.

For the example package, validate that the expected file is present, run the data load, reconcile accepted and rejected records, and archive the source only after the required checks pass. Explain what “success” means at each step. A file existing on disk does not prove that its upload is complete.

A useful follow-up is to describe the failure path. If reconciliation fails, retain the source and diagnostics, stop dependent work and identify the batch for investigation. Avoid a design where a notification task accidentally makes the overall run look successful despite a failed load.

2. How would you choose a Lookup cache mode?

Full cache loads reference data before processing; partial cache builds a cache as lookups occur; no cache queries without retaining lookup results. The choice affects memory, database traffic and data freshness. Also check comparison behavior: SSIS cached comparisons and database comparisons can differ, so changing cache mode may change matches. See Microsoft's Lookup transformation documentation.

For a small stable customer reference set, full cache may be reasonable. For a much larger set, measure the working set and database load before choosing. Treat those as hypotheses, not universal rules.

Build a fixture containing ABC, abc, a trailing-space variant, a missing key and duplicate reference keys. Define the intended business comparison and normalize both sides consistently when appropriate. Do not use normalization to erase distinctions that are meaningful in the source system.

3. What should happen to unmatched and invalid rows?

An unmatched customer and an invalid numeric amount are different failures. One may be a valid order waiting for reference data; the other may be malformed input. Give them distinguishable reason codes and an approved retry policy.

SSIS error outputs can send problematic rows to a separate destination. Microsoft describes adding understandable error and column information alongside the numeric diagnostics in its package troubleshooting guidance.

Include a batch identifier and enough source context to reproduce the issue without exposing unnecessary sensitive data. “Ignore failure” is not a complete data-quality strategy. Define which exceptions are acceptable, which stop the batch and who resolves them.

4. Can checkpoints restart a failed data flow halfway through?

No. SSIS checkpoint restart operates at the control-flow level. It does not resume partway through a Data Flow task. Loop behavior and transactions need particular care: work may run again, and Microsoft warns about combinations that can repeat an already committed transaction. Checkpoint files also need appropriate filesystem protection. Read restart packages by using checkpoints for the configuration and limitations.

A good answer explains the practical implication. If the order load wrote some records before failure, rerunning its Data Flow task must still produce a correct target. The checkpoint cannot decide whether an order is a duplicate business event.

In a practice environment, deliberately fail after an observable write and record what executes on restart. Compare the final target with a clean run. Document the result for the actual package instead of relying only on a diagram of the intended behavior.

5. How would you make a file load safe to repeat?

Choose a stable identity for both the batch and its business events. A filename alone can be reused, renamed or delivered twice. Preserve an input fingerprint and the source's event identifier when those are available, and define how corrections are represented. Beyond SSIS, safe reruns and stable identifiers are a recurring theme in data engineering pipeline design interviews.

Consider this original test fixture:

The first three rows should not create three separate orders under this policy. The fourth must not silently overwrite the accepted amount merely because it arrived later. If the source has a legitimate correction event, model it explicitly.

One possible design stages a batch, validates its internal consistency and applies accepted changes through a controlled database operation with uniqueness constraints. Explain the commit boundary and the recovery process. Do not promise that an external file move and a database commit are automatically one atomic operation.

6. When would you use a transaction?

Use a transaction when a set of supported changes must commit or roll back together. State which resources participate and what happens if a connection or task does not support the intended behavior. SSIS provides transaction configuration, but distributed infrastructure and component support affect the implementation. See Microsoft's Integration Services transactions.

For example, updating an order header and its lines should not leave a header marked complete with missing lines. However, keeping a very large import inside one transaction may create unacceptable locking or log pressure. Database internals interview questions on locking and write-ahead logging can help you explain why. Discuss bounded batches or staging as alternatives, with the business tradeoff made explicit.

Answer the follow-up before it is asked: what would the next run see if the previous connection dropped after the server committed but before the client received confirmation? Durable event identity and a reconciliation query help resolve that uncertainty.

7. How would you diagnose a slow package?

Measure source extraction, row transformation and target loading separately. Record input volume and compare equivalent runs. A slow query, an oversized row, a blocking transformation and a contended target require different changes. When the cause is a slow query, SQL query optimization interview questions help you practise reading execution plans and choosing indexes.

For our example, first run the source query independently under appropriate read-only access. Then inspect where rows accumulate or throughput changes in the package. Finally examine target waits and write behavior with the database owner. Do not start by adjusting every buffer setting you can find.

A useful interview answer gives one controlled experiment: reduce unused source columns, run the same fixture, compare elapsed time and confirm identical accepted and rejected business results. Explain when the experiment would fail to support your hypothesis.

8. What belongs in deployment and operational handover?

Describe the environment-specific values, secret handling, execution identity, logging, alerting and rollback plan. Show how test and production targets stay distinct. Avoid embedding credentials in a package example or assuming the identity used in the designer matches the identity used by the scheduler.

Prepare a short runbook: expected input, normal duration range, reconciliation query, common rejection reasons, retry conditions and escalation owner. Include the question that determines whether a rerun is safe. “Run it again and see” is not enough when the process can create financial or customer records.

Practise the explanation with a teammate or with PhantomCodeAI during preparation. Keep examples synthetic, label training work honestly and follow the rules of any actual assessment.

Frequently asked questions

Are SSIS interview questions mostly definitions?

Some begin with definitions, but prepare to apply them. A small package and a repeatable failure scenario give you evidence for explaining reliability, performance and data quality.

Does a successful package prove every input row loaded?

No. Reconcile source, accepted, rejected and intentionally excluded records according to the package's rules. Success status alone does not explain the business result.

Should every lookup use full cache?

No. Evaluate reference size, freshness, memory and comparison behavior. Test representative keys and measure the chosen approach instead of treating a cache mode as a universal optimization.

What should I say if I have not operated SSIS in production?

Describe the package you built, the failures you tested and the limitations of the exercise. A clear account of real training work is stronger than invented production scale or responsibilities.