Datasheet

Transformations
Transformations are key components to the data flow that change the data to a desired format. For exam-
ple, you may want your data to be sorted and aggregated. Two transformations can accomplish this task
for you. The nicest thing about transformations in SSIS is that it’s all done in-memory and it no longer
requires elaborate scripting as in SQL Server 2000 DTS. The transformation is covered in Chapters 4 and 6.
Here’s a complete list of transforms:
❑ Aggregate: Aggregates data from transform or source.
❑ Audit: The transformation that exposes auditing information to the package, such as when the
package was run and by whom.
❑ Character Map: This transformation makes 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: This transformation allows you to export a column from the data flow to a file.
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.
13
Welcome to SQL Server Integration Services
04 584359 ch01.qxd 12/16/05 10:20 PM Page 13