Roadmap › SSIS — SQL Server Integration Services › Module: Data Flow
Lessons in this course (13)
6. Transformations and their types
Intermediate★ Must knowLesson 6 of 13
| Transformation | Purpose | Blocking type |
|---|---|---|
| Derived Column | New/replace column via expression | Non-blocking (synchronous) |
| Data Conversion | Convert data types (e.g. DT_STR → DT_WSTR) | Non-blocking |
| Conditional Split | Route rows by conditions (like CASE) | Non-blocking |
| Multicast | Send all rows to many outputs | Non-blocking |
| Lookup | Match against reference data | Non-blocking (synchronous) |
| Row Count | Count rows into a variable | Non-blocking |
| Copy Column, Character Map, Audit | Simple row operations | Non-blocking |
| OLE DB Command | Run SQL per row (slow!) | Non-blocking but row-by-row |
| Script Component | Custom C# (source, transformation or destination) | Depends |
| Merge, Merge Join, Union All | Combine inputs (Merge/Merge Join need sorted input) | Semi-blocking |
| Pivot / Unpivot | Reshape columns/rows | Semi-blocking |
| Sort | Sort rows (optionally remove duplicates) | Fully blocking |
| Aggregate | GROUP BY, SUM, COUNT... | Fully blocking |
| Fuzzy Lookup / Fuzzy Grouping | Approximate matching, dedup | Fully blocking |
| Slowly Changing Dimension | SCD wizard (types 0, 1, 2) | Row-by-row |
| Percentage/Row Sampling, Term Extraction | Sampling/text mining | Varies |
Synchronous (non-blocking): output rows = input rows, same buffer, fastest. Asynchronous: new buffers; semi-blocking passes some rows before all input is read; fully blocking must read all input before output (Sort, Aggregate) → memory heavy.