Datasheet

Chapter 1: Welcome to SQL Server Integration Services
16
Transform Components in the SSIS Data Flow affect data in the data pipe in - memory. This is not always
the panacea for ETL processing, especially under high - volume data processing. However, the latest
version of SSIS has changed the way the Data Flow Task breaks down the execution tree for the
transforms to take full advantage of asynchronous processing and parallelism to get the most from
multi - processor machines. Here ’ s a complete list of transforms:
Aggregate: Aggregates data from transform or source.
Audit: Exposes auditing information from the package to the data pipe, such as when the
package was run and by whom.
Character Map: Makes common string data changes for you, such as changing data from
lowercase to uppercase.
Conditional Split: Splits the data based on certain conditions being met. For example, this
transformation could be instructed to send data down a different path if the State column is
equal to Florida.
Copy Column: Adds a copy of a column to the transformation output. You can later transform
the copy, keeping the original for auditing purposes.
Data Conversion: Converts a column ’ s data type to another data type.
Data Mining Query: Performs a data - mining query against Analysis Services.
Derived Column: Creates a new derived column calculated from an expression.
Export Column: Exports a column from the Data Flow to the file system. For example, you can
use this transformation to write a column that contains an image to a file.
Fuzzy Grouping: Performs data cleansing by finding rows that are likely duplicates.
Fuzzy Lookup: Matches and standardizes data based on fuzzy logic. For example, this can
transform the name Jon to John.
Import Column: Reads data from a file and adds it into a Data Flow.
Lookup: Performs a lookup on data to be used later in a transformation. For example, you can
use this transformation to look up a city based on the zip code.
Merge: Merges two sorted data sets into a single data set in a Data Flow.
Merge Join: Merges two data sets into a single data set using a join function.
Multicast: Sends a copy of the data to an additional path in the workflow.
OLE DB Command: Executes an OLE DB command for each row in the Data Flow.
Percentage Sampling: Captures a sampling of the data from the Data Flow by using a
percentage of the total rows in the Data Flow.
Pivot: Pivots the data on a column into a more non - relational form. Pivoting a table means that
you can slice the data in multiple ways, much like in OLAP and Excel.
Row Count: Stores the row count from the Data Flow into a variable.
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
❑
c01.indd 16c01.indd 16 8/28/08 12:01:21 PM8/28/08 12:01:21 PM