A Table Summary of Key SSIS 2008 Data Transformations

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

One thought on “A Table Summary of Key SSIS 2008 Data Transformations

  1. Thanks for the list, i was looking for something like this!!

    Are these the only ones that i would need to rememeber for the MCTS exam…there seem to be a lot more in SSIS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s