SSIS Interview Questions for 2026: 16 Real Answers, From Data Flow Internals to ADF Migration

SQL Server Integration Services is Microsoft's ETL and workflow engine that ships with SQL Server. You build packages — .dtsx files, which are XML under the hood — in Visual Studio with the SSIS extension (SSDT), and run them via dtexec, SQL Agent jobs, or the SSISDB catalog. A package extracts data from sources, transforms it in memory through a pipeline of components, and loads destinations. In the MSBI stack, SSIS is the integration layer that feeds the warehouse, SSAS provides analytical models on top, and SSRS handles reporting. SSIS is not only data movement: the control flow also orchestrates file operations, FTP, T-SQL execution, process launches, and scripting, which is why teams use it as a general batch-workflow engine around the warehouse, not just as a loader.

Control flow is the orchestration layer of a package: tasks and containers wired together with precedence constraints. It defines order, branching, looping, and parallelism, and each task completes with a success or failure outcome. The Data Flow Task is one task inside the control flow; opening it drops you into the data flow, where the pipeline engine streams rows through memory buffers from sources, through transformations, to destinations. The practical distinction: control flow is task-based and deals in units of work — run this SQL, loop over these files, then load — while data flow is row- and buffer-based and deals in data itself. A package has exactly one control flow but can contain many Data Flow Tasks, each with its own pipeline. Interviewers use this question to check you understand SSIS has two distinct engines with different behavior.

Precedence constraints are the arrows connecting control-flow tasks, and they decide whether the next task runs. Three outcome types: Success (green), Failure (red), and Completion (blue, meaning run regardless of outcome). Each constraint has an evaluation operation: Constraint only, Expression only, Expression AND Constraint, or Expression OR Constraint — the expression is an SSIS expression over variables, letting you branch on data conditions, not just task outcomes. When a task has multiple incoming constraints, you choose Logical AND (solid lines, all must be satisfied) or Logical OR (dotted lines, any one suffices). Typical use: success path continues the load, failure path branches to a notification task, and an expression-based constraint skips a load step when a row-count variable is zero. It is a simple question, but fumbling the expression options flags shallow experience.

Parameters, introduced with the project deployment model in SQL Server 2012, are the external inputs of a package. They are project- or package-scoped, set at execution time, read-only while the package runs, can be marked Required or Sensitive, and in the SSISDB catalog they bind to environment variables so the same package picks up dev, UAT, or prod values. Variables are internal working state: scoped to the package or a container, changeable at runtime, usable in expressions (with EvaluateAsExpression), loop counters, Script Tasks, and property expressions. The clean mental model interviewers want: a parameter carries configuration in from outside; a variable holds state the package computes for itself. Saying you would use a variable for an environment-specific connection string, instead of a parameter mapped to an environment, signals you have not worked with SSISDB deployments.

Derived Column is a synchronous, row-level transformation that adds new columns or replaces existing ones using the SSIS expression language: string manipulation, explicit type casts like (DT_WSTR, 50), date functions, conditional logic with the ternary operator, and null handling with REPLACENULL or ISNULL. Because it is synchronous and non-blocking, it operates on rows within the existing buffer and is essentially free from a pipeline-performance perspective. Two details worth volunteering: first, SSIS expressions are their own language, not T-SQL — functions and casting syntax differ, and type mismatches must be cast explicitly; second, expression failures such as a bad cast on dirty data can be handled per-column through the transformation's error output, redirecting offending rows instead of failing the whole data flow. That second point often leads the interviewer straight into the error-handling discussion.

Full cache, the default, loads the entire reference query into memory before the data flow starts; no database calls happen per row. Because matching happens in the SSIS engine, string comparisons are case- and accent-sensitive — a classic bug source when the database collation is case-insensitive. Partial cache queries the database on a cache miss and keeps matched rows in a size-limited cache, useful for large reference sets where only a fraction is touched. No cache queries the database for essentially every row — for small or highly volatile reference data. For rows with no match you choose: fail the component (default), ignore failure (reference columns become NULL), redirect to the no-match output, or redirect to the error output. Bonus point: the Cache Transform with a Cache Connection Manager lets you build a cache once and reuse it across multiple Lookups or packages.

Use Lookup when you are enriching a stream against a reference set — the classic dimension-key lookup. It needs no sorted inputs, caches the reference data, and supports only equi-joins; in full-cache mode it returns a single match per key and warns about duplicates in the reference set. Use Merge Join when you genuinely need join semantics: inner, left outer, or full outer, including one-to-many results. Its cost is that both inputs must be sorted — either via the fully blocking Sort transformation or, far better, by sorting in the source query with ORDER BY and marking the source output IsSorted with SortKeyPosition set. Merge Join is semi-blocking and asynchronous, so it also copies data into new buffers. Rule of thumb: small-to-medium reference set, Lookup with full cache; two large pre-sorted streams or outer-join requirements, Merge Join with sorting pushed to SQL.

Non-blocking (synchronous) transformations process each row in place within the same buffer — Derived Column, Data Conversion, Conditional Split, Multicast, Row Count. They are cheap. Semi-blocking (asynchronous) transformations can emit rows before consuming all input but write output to new buffers — Merge, Merge Join, Union All. They add memory copies and break the one-buffer flow. Fully blocking transformations must consume the entire input before producing a single output row — Sort, Aggregate, Fuzzy Lookup, Fuzzy Grouping — which means the whole dataset sits in memory (or spills to disk) and the pipeline stalls behind them. The classification matters because it is the backbone of every SSIS performance conversation: a Sort dropped into a 50-million-row flow is a memory bomb, and the correct fix is usually pushing sorting and aggregation into the source SQL, where the database does it with indexes.

Pick the change-detection mechanism first. A high-water-mark design stores the last loaded ModifiedDate or ID in a control table; the source query filters beyond the watermark via a parameterized query or expression, and the watermark updates only after a successful load. If the source is SQL Server with CDC enabled, the CDC Control Task, CDC Source, and CDC Splitter give you insert, update, and delete streams without trusting timestamp columns. Change Tracking is a lighter alternative. When the source has no reliable change markers, you fall back to full extract with hash comparison against the target. For the load itself: Lookup on the business key — no-match output fast-loads inserts; match output goes through change detection, and genuine changes are staged to a work table, then applied with one set-based UPDATE or MERGE in an Execute SQL Task. Never use OLE DB Command for volume updates — it fires per row.

Checkpoints let a failed package restart from the failed task instead of the beginning. You set CheckpointFileName, CheckpointUsage to IfExists, and SaveCheckpoints to True; as tasks complete, SSIS records progress and variable values in the checkpoint file, and on rerun it skips completed tasks. The limitations are what interviewers actually probe. Granularity is the control-flow task: a failed Data Flow Task reruns from its first row — there is no mid-stream resume. Loop containers and containers enlisted in transactions rerun as a whole unit. Object-typed variables are not persisted. And checkpoints interact badly with transactions, since a transacted container's rollback forces a full rerun of that container anyway. So the honest answer: checkpoints handle control-flow restartability, but data-flow restartability must be designed — idempotent loads, staged batches, or watermark commits that make a rerun safe.

At row level, sources, transformations, and destinations expose error outputs configurable per column: fail the component, ignore (NULL out the value), or redirect the row. Redirected rows carry ErrorCode and ErrorColumn, and the standard pattern lands them in a reject table with audit columns for investigation and reprocessing. At task and package level, event handlers — OnError, OnTaskFailed, OnWarning — fire and bubble up the container hierarchy unless the Propagate system variable is set false, and are typically used for alerting and cleanup. For logging, the package deployment model uses log providers: SQL Server (the sysssislog table), text or XML files, or the Windows event log. The project deployment model largely replaces those with SSISDB catalog logging — levels None, Basic, Performance, and Verbose, plus custom levels from SSIS 2016 — queryable through catalog.executions and catalog.operation_messages and the built-in catalog reports.

The package deployment model is the legacy approach: individual .dtsx files deployed to the file system or msdb, configured through package configurations — XML files, a SQL Server configuration table, environment variables, registry entries, or parent-package variables — and executed with dtexec, often with /SET overrides. The project deployment model, default since SQL Server 2012, builds the whole project into an .ispac and deploys it to the SSISDB catalog. Parameters replace configurations; catalog environments hold named variable sets that map onto parameters at execution, so switching dev, UAT, and prod is a reference change, not a file edit. The catalog adds built-in execution logging, operational reports, a T-SQL execution API, and project versioning with rollback to previous versions. When asked why the project model wins: centralized configuration, clean environment promotion, and monitoring you do not have to build yourself.

Type 2 preserves history: attribute changes close the current row (EndDate, IsCurrent = 0) and insert a new version with a fresh surrogate key. The SCD Wizard will generate this, but it is rarely production-grade: its updates run through OLE DB Command, executing per row, which collapses at volume; and re-running the wizard regenerates the pipeline, destroying any customization. The production pattern: Lookup against current dimension rows on the business key. No-match output fast-loads brand-new members. Match output goes through change detection — cleanest is comparing a hash computed over the tracked attributes, otherwise explicit column comparisons in a Conditional Split. For changed rows, insert the new version via fast load, and stage the keys of expiring rows to a work table, then close them with one set-based UPDATE in an Execute SQL Task. Alternatively, land everything in staging and run a T-SQL MERGE. Both scale; the wizard does not.

Measure before touching anything: isolate whether source, transformations, or destination is the bottleneck — for example, temporarily replacing the destination to test pure read-and-transform speed. Then, in rough order of payoff: narrow the rows — select only needed columns with right-sized data types, so each buffer holds more rows. Tune buffers: DefaultBufferMaxRows (default 10,000) and DefaultBufferSize (default 10 MB), or AutoAdjustBufferSize on SSIS 2016+. Eliminate fully blocking transformations by pushing Sort and Aggregate into source SQL, and never use OLE DB Command in the flow. At the destination, use fast load with table lock and a sensible commit size, and consider disabling nonclustered indexes for bulk loads. Trim Lookup reference queries so full cache stays lean. Exploit parallelism via MaxConcurrentExecutables and independent data flows. Finally, watch the Buffers Spooled counter — spooling means memory pressure, and BufferTempStoragePath should point at fast disk.

Every task and container has TransactionOption: Supported (default — joins a parent transaction if one exists), Required (starts one if none exists), or NotSupported. Required transactions run through MSDTC, so the Distributed Transaction Coordinator service must be running on every machine involved; that brings coordination overhead and fails outright with providers or environments that cannot enlist. The common alternative is manual transaction control: Execute SQL Tasks issuing BEGIN TRAN, COMMIT, and ROLLBACK, with RetainSameConnection = True on the connection manager so all tasks share one session — lighter, but you own the error paths. The restartability interaction is the trap: checkpoints and transactions work against each other, because a transacted container rolls back and reruns as a single unit, defeating task-level restart inside it. The mature answer: prefer idempotent, re-runnable designs — truncate-and-load staging, watermark updates committed with the data — over wrapping long pipelines in one distributed transaction.

Two paths. Lift-and-shift: provision an Azure-SSIS Integration Runtime in ADF — managed VMs running the SSIS engine — host SSISDB in Azure SQL Database or Managed Instance, deploy your existing .ispac projects, and invoke packages with the Execute SSIS Package activity, replacing SQL Agent schedules with ADF triggers. It is fast and low-risk for large estates, but third-party components and custom assemblies need custom setup scripts on the IR, and you keep old patterns plus always-on or scheduled IR costs. Rebuild: re-author natively — Copy activities for movement, Mapping Data Flows (Spark-based) for transformation, pipelines and triggers for orchestration; connection managers become linked services, and logging moves to ADF monitoring. Cloud-native and scalable, but it is a genuine rewrite with a different execution model. The interview-winning move is starting with estate assessment: inventory packages, flag Script Tasks and third-party components, lift-and-shift the long tail, rebuild the high-value pipelines.