SSIS 阻塞，半阻塞和全阻塞 (Non-blocking, semi-blocking and Fully-blocking) transformations清单
三种Blocking类型，这里跟数据流的Buff关系很大:
■■ non-blocking transformations,每一行直接转换输出，没有等待.
■■ partial-blocking transformation，直到存储一定数量的好才输出。
■■ blocking transformation, 在输出前必须先读入所有行。
Logical Row-Level Transformations
| Data flow transformation | purpose | blocking type |
| Audit | Adds additional columns to each row based on sys- tem package variables such as ExecutionStartTime and PackageName. | N |
| Cache Transform | Allows you to write data to a cache with the Cache con- nection manager. The data can then be used by the Lookup transformation. This is useful if you are using multiple Lookup transformations against the same data, because SSIS will cache the needed data only once and not for each Lookup component. | N |
| Character Map | Performs common text operations such as Uppercase and allows advanced linguistic bit-conversion operations. | N |
| Copy Column | Duplicates column values in each row to a new named column. | N |
| Data Conversion | Creates a new column in each row based on a new data type converted from the existing column. An example is converting text to numeric data or text to Unicode text. | N |
| Data flow transformation | purpose | blocking type |
| Derived Column | Creates or replaces a column for each row based on a specified SSIS expression. This is the most often used logical row-level transformation because it enables the replacement of column values or the creation of new columns based on existing columns, variables, and parameters. |
N |
| Export Column | Exports binary large objects (BLOB) columns, one row at a time, to a file. | N |
| Import Column | Loads binary files such as images into the pipeline; intend- ed for a BLOB data type destination. | N |
| Row Count | Tracks the number of rows that flow through the transfor- mation and stores the number in a package variable after the final row. | N |
Multi-Input and Multi-Output Transformations
| Data flow transformation | purpose | blocking type |
| CDC Splitter | Splits a single flow of changed rows from the CDC source component into multiple data flows based on the type of the source data change (that is, whether it is an insert, update, or delete operation). CDC Splitter routes the data based on the __$operation column into three possible outputs. this transformation is like a specific version of the Conditional Split transformation that automatically handles the standard values of the __$operation column. |
N |
| Conditional Split | Routes or filters data based on a Boolean expression to one or more outputs, from which each row can be sent out only one output path. | N |
| Lookup | Performs a lookup operation between a current row and an external dataset on one or more columns. Additional columns can be added to the data flow from the external dataset. | N |
| Merge | Combines the rows of two similar sorted inputs, one on top of the other, based on a defined sort key. | P |
| Merge Join | Joins the rows of two sorted inputs based on a defined join column or columns, adding columns from each source. | P |
| Multicast | Generates one or mode identical outputs, from which every row is sent out every output. This transformation creates a logical copy of the data. | N |
| Union All | Combines one or more similar inputs, stacking rows one on top of another, based on matching columns. The number of rows in the output of Union All is the combined row counts of all the inputs. | P |
Multi-Row Transformations
| Data flow transformation | purpose | blocking type |
| Aggregate | Associates rows based on defined grouping and generates aggregations such as SUM, MAX, MIN, and COUNT. | B |
| Percent Sampling | Filters the input rows by allowing only a defined percent to be passed to the output path. | N |
| Pivot | Takes multiple input rows and pivots the rows to generate an output with more columns based on the original row values. | P |
| Row Sampling | Generates a fixed number of rows, sampling the data from the entire input, no matter how much larger than the defined output the input is. | B |
| Sort | Orders the input based on defined sort columns and sort directions. The Sort transformation also allows the removal of duplicates across the sort columns. | B |
| Unpivot | Takes a single row and generates multiple rows, moving column values to the new row based on defined columns. | P |
Advanced Data-Preparation Transformations
| Data flow transformation | purpose | blocking type |
| DQS Cleansing | Validates rows by automatically per- forming data cleansing using an exist- ing knowledge base in Data Quality Services (DQS). | P |
| OLE DB Command | Performs database operations such as updates or deletions, one row at a time, based on mapped parameters from input rows. | N |
| Slowly Changing Dimension | Generates transformations necessary to support loading dimension tables in data warehouse scenarios. This transformation handles SCD (Slowly Changing Dimension) Type 1 and Type 2 and also has support for inferred members. Chapter 7 focuses on this transformation. | N |
| Data Mining Query | Applies input rows against a data min- ing model for prediction. | P |
| Fuzzy Grouping | Performs de-duplication based on similarity of string values in selected columns. | B |
| Fuzzy Lookup | Joins a data flow input to a reference table based on column similarity. The Similarity Threshold setting specifies the closeness of allowed matches—a high setting means that matching val- ues are close in similarity. | B |
| Script Component | Applies custom .NET scripting capabilities against rows, columns, inputs, and outputs in the data flow pipeline. This is the most powerful component. Chapter 19, “Implementing Custom Code in SSIS Packages” looks at some of its possibilities. | N |
| Term Extraction | Analyzes text input columns for English-language nouns and noun phrases. | P |
| Term Lookup | Analyzes text input columns against a user-defined set of words for associa- tion. | P |
