SSIS — SQL Server Integration Services

In short: SQL Server Integration Services is Microsoft's ETL and workflow tool shipped with SQL Server.

• It extracts data from many sources (SQL, Oracle, flat files, Excel, XML, APIs), transforms it in a high-performance in-memory pipeline, and loads it into targets such as data warehouses.
• Also used for data migration, file automation and database maintenance.
• Packages are built in Visual Studio and deployed to the SSISDB catalog.

In short: Control Flow orchestrates the workflow: tasks (Execute SQL, Data Flow, File System, Script, Send Mail), containers and precedence constraints determining execution order.

• Data Flow is inside a Data Flow Task and moves rows from sources through transformations to destinations in memory buffers.
• Control Flow deals with tasks; Data Flow deals with rows.

Sequence Container (groups tasks, scopes variables, shares transactions, lets you enable/disable a group), For Loop Container (repeats while a condition holds — counter-based), Foreach Loop Container (iterates over a collection: files in a folder, rows of an ADO recordset, items, nodes, blobs), and the Task Host container that wraps every single task.

In short: A connector between control-flow executables that controls whether the next task runs, based on the outcome of the previous one: Success, Failure or Completion, optionally combined with an expression (e.g. only if @RowCount > 0).

• With multiple constraints into one task, choose logical AND (all) or OR (any).

In short: Parameters (introduced in 2012) pass values into a package or project from outside before execution — server names, folders, dates — and are set from SSISDB environments or dtexec.

• They are read-only during execution.
• Variables store values during execution (row counts, file names, result sets), can be changed by tasks/scripts, and are scoped to package, container or task.

In short: Synchronous (non-blocking) transformations process rows in the same buffer and output exactly one row per input row — Derived Column, Data Conversion, Conditional Split, Lookup, Multicast, Row Count.

• Asynchronous transformations create new buffers.
• Semi-blocking ones (Merge, Merge Join, Union All, Pivot) can output before reading all input;
• fully blocking ones (Sort, Aggregate, Fuzzy Grouping/Lookup) must read all input first, consuming memory and slowing large flows.

In short: Full cache loads the whole reference query into memory before processing (fastest, needs memory, reference must be stable).

• Partial cache starts empty and queries the database on misses, caching the results (good for large references with few used keys).
• No cache queries the database for every row (slowest; use when the reference changes during the load).
• A Cache Connection Manager can pre-build a cache from any source and reuse it across lookups/packages.

In short: At row level, configure component error outputs to redirect failing rows (conversion/truncation/lookup no-match) to error tables with ErrorCode/ErrorColumn instead of failing.

• At task level, use precedence constraints on Failure to run cleanup/notification tasks, event handlers (OnError logs to an audit table and sends email), MaximumErrorCount, and checkpoints for restart.
• Logging in SSISDB captures messages; SQL Agent alerts on job failure.

In short: Store the last successful load watermark (date/rowversion) in a control table;

• Execute SQL Task reads it into a variable;
• the source query extracts rows changed since then;
• Lookup against the target splits new (No Match → insert) vs existing rows (Match → conditional split on changes → staging table → set-based UPDATE);
• final Execute SQL Task updates the watermark.
• Alternatives: CDC components, or load staging and run a T-SQL MERGE.

In short: The package deployment model (legacy) deploys individual .dtsx files to the file system or msdb and uses package configurations.

• The project deployment model (2012+) deploys the whole project as an .ispac to the SSISDB catalog, uses project/package parameters with environments per server, keeps versions, and provides built-in logging and execution reports.
• New work should use the project model.

In short: Filter and select only needed columns at the source;

• sort in SQL and mark outputs as sorted;
• remove blocking transformations;
• replace OLE DB Command and SCD wizard with staging + set-based SQL;
• use full-cache lookups with minimal columns;
• fast-load destinations with table lock and batch settings;
• drop/disable indexes during huge loads;
• tune buffer size/rows (or AutoAdjustBufferSize);
• run independent flows in parallel (MaxConcurrentExecutables, EngineThreads);
• avoid unnecessary data conversions;
• check for buffer spooling and server memory;
• switch to incremental loads.

In short: Identify the column from the error (ErrorColumn ID → lineage ID/name in the advanced editor or catalog messages).

• Short term: configure that component's error output to redirect truncated rows to an error table so the rest loads, and alert.
• Root cause: the source started sending longer values — confirm with data profiling, then widen the staging/target column and the SSIS metadata (refresh external columns), or apply a business rule (trim/substring) agreed with the business.
• Add a data-quality check that reports length violations.

In short: Foreach Loop Container (File enumerator on the inbound folder, *.csv, map the file name to a variable).

• Inside: Flat File connection manager with its ConnectionString set by an expression from the variable;
• Data Flow (Flat File Source → Data Conversion/validation → error output to an error table → OLE DB Destination fast-load to staging);
• Execute SQL to merge staging into targets and log the file name/BatchId;
• File System Task on Success moves the file to Archive (with timestamp), on Failure moves it to Error.
• Event handler for alerts.
• Set DelayValidation=True because the file doesn't exist at design validation time.