Integration Services uses transformations to manipulate data during an ETL dataflow. Transformations can be used in Data Flow Tasks, between data source and destination components, or other transformations.

The Unpivot transformation can help in normalizing of a dataset during the load process of a data warehouse.

For example, if the source dataset looks like this (five columns):

Unpivot Source Dataset

Then it can be transformed to another like this (three columns):



The transformation has an editor which should be configured this way (for this example):


Note that the Pivot key value column name is set to Period, where we would like to send the unpivoted column names. All the unpivoted input column values will be sent to the Value field - which did not exist before the transformation, it is created in the output buffer.

This transformation supports an input buffer and an output buffer. It looks like this in the data flow editor of BIDS (UPT Unpivot):


This is a partially blocking transformation because of the introduction of the new buffers (and because of the transformation does not have to wait all data, writing to the output buffers can be started as soon as a row arrives to the input buffer and the new row values are calculated).

Since there is a one-to-many relationship between the input and output buffers, this is an asynchronous transformation.

So, the Unpivot transformation can be used for the normalization of a dataset on the fly - unlike the Pivot transformation, which is helpful when a denormalized result set is needed.

For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check this Wiki article.