Integration Services Transformations
SQL Server 2005 Integration Services (SSIS) transformations are the components in the data flow of a package that aggregate, merge, distribute, and modify data. Transformations can also perform lookup operations and generate sample datasets. This section describes the transformations that Integration Services includes and explains how they work.
Business Intelligence Transformations
The following transformations perform business intelligence operations such as cleaning data, mining text, and running data mining prediction queries.
| Transformation | Description |
|---|---|
The transformation that standardizes values in column data. |
|
The transformation that looks up values in a reference table using a fuzzy match. |
|
The transformation that extracts terms from text. |
|
The transformation that looks up terms in a reference table and counts terms extracted from text. |
|
The transformation that runs data mining prediction queries. |
Row Transformations
The following transformations update column values and create new columns. The transformation is applied to each row in the transformation input.
| Transformation | Description |
|---|---|
The transformation that applies string functions to character data. |
|
The transformation that adds copies of input columns to the transformation output. |
|
The transformation that converts the data type of a column to a different data type. |
|
The transformation that populates columns with the results of expressions. |
|
The transformation that uses script to extract, transform, or load data. |
|
The transformation that runs SQL commands for each row in a data flow. |
Rowset Transformations
The following transformations create new rowsets. The rowset can include aggregate and sorted values, sample rowsets, or pivoted and unpivoted rowsets.
| Transformation | Description |
|---|---|
The transformation that performs aggregations such as AVERAGE, SUM, and COUNT. |
|
The transformation that sorts data. |
|
The transformation that creates a sample data set using a percentage to specify the sample size. |
|
The transformation that creates a sample data set by specifying the number of rows in the sample. |
|
The transformation that creates a less normalized version of a normalized table. |
|
The transformation that creates a more normalized version of a nonnormalized table. |
Split and Join Transformations
The following transformations distribute rows to different outputs, create copies of the transformation inputs, join multiple inputs into one output, and perform lookup operations.
| Transformation | Description |
|---|---|
The transformation that routes data rows to different outputs. |
|
The transformation that distributes data sets to multiple outputs. |
|
The transformation that merges multiple data sets. |
|
The transformation that merges two sorted data sets. |
|
The transformation that joins two data sets using a FULL, LEFT, or INNER join. |
|
The transformation that looks up values in a reference table using an exact match. |
Other Transformations
Integration Services includes the following transformations to export and import data, add audit information, count rows, and work with slowly changing dimensions.
| Transformation | Description |
|---|---|
The transformation that inserts data from a data flow into a file. |
|
The transformation that reads data from a file and adds it to a data flow. |
|
The transformation that makes information about the environment available to the data flow in a package. |
|
The transformation that counts rows as they move through it and stores the final count in a variable. |
|
The transformation that configures the updating of a slowly changing dimension. |
Custom Transformations
You can also write custom transformations. For more information, see Developing a Custom Transformation Component with Synchronous Outputs and Developing a Custom Transformation Component with Asynchronous Outputs.
See Also
Concepts
Integration Services Paths
Data Flow Elements
Other Resources
Integration Services Sources
Integration Services Destinations