DLDevLadder📒 My Notebook
Roadmap › 2. Database, ETL & BI › Step 31
IS

SSIS — SQL Server Integration Services

Packages, control flow and data flow, connection managers, variables and parameters, containers, transformations, lookups, SCD, error handling, logging, checkpoints, deployment models, SSISDB catalog, scheduling and performance tuning.
Quick revision for interview day. Print it or save it as a PDF.

Building blocks

Package (.dtsx) · Project (.ispac) · Control Flow (tasks, containers, precedence constraints) · Data Flow (sources, transformations, destinations) · Connection managers · Variables · Parameters · Event handlers · Log/SSISDB.

Key tasks

Execute SQL · Data Flow · Execute Package · File System · FTP · Script · Send Mail · Expression · Bulk Insert · CDC Control.

Transformations by blocking type

  • Non-blocking: Derived Column, Data Conversion, Conditional Split, Lookup, Multicast, Row Count, Copy Column, Audit.
  • Semi-blocking: Merge, Merge Join, Union All, Pivot/Unpivot.
  • Fully blocking: Sort, Aggregate, Fuzzy Lookup/Grouping.

Lookup cache

Full (default, fastest) · Partial · No cache · Cache Connection Manager. Case/space/type sensitive.

Error handling

Error output: Fail / Ignore / Redirect row · OnError event handler · MaximumErrorCount · Propagate=False · checkpoints · audit table.

Deployment

Project model → SSISDB → folder → environments (DEV/QA/PROD) → parameter mapping → SQL Agent job → reports. dtexec /ISServer ... /Env n /Par ....

Performance

SQL source with filters · no Sort/Aggregate on big data · no OLE DB Command · full-cache lookups · fast load + table lock · buffer tuning · parallel tasks · staging + MERGE · incremental.