This website requires JavaScript.

SSIS 阻塞,半阻塞和全阻塞

三种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