SSIS — SQL Server Integration Services

SSIS (SQL Server Integration Services) is Microsoft's ETL and workflow platform, part of SQL Server. You build packages (.dtsx XML files) visually in Visual Studio, then deploy them to the SSIS Catalog (SSISDB) and run them with SQL Server Agent.

A package has two main designers:

• Control Flow — the workflow: tasks (Execute SQL, Data Flow, File System, Script, Send Mail...) connected by precedence constraints, grouped in containers (Sequence, For Loop, Foreach Loop).
• Data Flow — the row pipeline inside a Data Flow Task: sources → transformations → destinations, processed in memory buffers.

Typical uses: loading data warehouses, migrating data, importing/exporting files, cleansing data, automating database maintenance.

Design-time tooling has moved from BIDS (2005–2008 R2) → SSDT (2012–2017) → the "SQL Server Integration Services Projects" extension for Visual Studio 2019/2022/2026.

Package development lifecycle: requirements → design (source-to-target mapping) → develop in Visual Studio (SSIS project) → debug with data viewers/breakpoints → configure parameters → build (.ispac) → deploy to SSISDB (dev → test → prod) → configure environments per server → schedule with SQL Agent → monitor via SSISDB reports → maintain.

Package execution lifecycle:

1. Package loads; validation of tasks and connections (can be delayed with DelayValidation=True).
2. Parameters/variables are set (from environment references, /SET options, or expressions).
3. OnPreExecute events fire; Control Flow executes tasks in order determined by precedence constraints (Success/Failure/Completion, optionally with expressions).
4. For each Data Flow Task: the pipeline engine allocates buffers, sources read rows, transformations process buffers, destinations write; error outputs redirect bad rows.
5. Event handlers (OnError, OnWarning, OnPostExecute, OnTaskFailed...) run when events bubble up.
6. Logging writes to SSISDB catalog views (or log providers).
7. Checkpoints (if enabled) record completed tasks so a restart resumes after the failure point.
8. Package completes with Success/Failure result.

Data flow buffer lifecycle: source fills a buffer (default DefaultBufferMaxRows 10,000 rows / DefaultBufferSize 10 MB) → synchronous transformations modify the same buffer in place → asynchronous transformations copy rows into new buffers → destination writes the buffer → buffer reused.