Data Transformations change data between data sources input and data destination output. In SSIS 2008, there are quite a few to choose from, so the choice of transformation is not always immediately clear. Further, the presence or absence of an error output may affect the choice of transformation. Here, some of the key transformations are provided, along with the input they will take (single or multiple) and whether an error output is part of the transformation or not.
Row Level Transformations
The following transformations act on at the row level, without requiring other rows to operate.
|
Data Transformation Name |
Input |
When to use it… |
Support error output? |
|
Character Map |
One input |
String data only; case change, character width |
Yes |
|
Copy |
One input |
Copy of an existing column |
No |
|
Data Conversion |
One input |
Changing data type for columns |
Yes |
|
Derived Column |
One input |
More complex transformations using variables and expressions |
Yes |
Multiple Input and Output Transformations
Other Transformations operate with more than multiple data inputs or multiple data outputs. Note that Merge and Merge join require sorted inputs, and can match more than one row over join columns. The sort position is retained after the transformation is completed.
Union All stacks similarly-structured rows together, rather than knitting rows together over a join as Merge does. Further, it does not require sorted inputs.
|
Data Transformation Name |
Input |
When to use it… |
Support error output? |
|
Conditional Split |
One data stream |
Divides the data stream into separate pieces dependent on some condition; sends the output row to a distinct output stream |
Yes |
|
Multicast Transform |
One data stream |
Replicates the data stream; sends the output row to every output stream |
No |
|
Union All |
Multiple data stream |
Unite unsorted multiple data streams that have similar structures |
No |
|
Merge |
Multiple data stream |
Unite sorted two data streams that have similar structures and matching metadata; easier for ordering output |
No |
|
Merge Join |
Multiple data stream |
Unites sorted two data streams; this can do left, full or inner joins. Merges rows based on common columns. |
No |
|
Lookup |
Multiple data stream |
This is useful for transforming a source code to something user-friendly. Match/No Match output |
Yes |
To add this post to Twitter, please click here.
Hopefully that will help someone, somewhere to remember the details for the exam J
