SSIS Unpivot Transformation

[[articles:SQL Server Integration Services (SSIS)|Integration Services]] uses transformations to manipulate data during an [[articles:Extract Transform Load (ETL)|ETL]] dataflow. Transformations can be used in [[articles:SSIS Dataflow Task|Data Flow Tasks]], between data source and destination components, or [[articles:SSIS: List of Transformations|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):

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 [[articles:SSIS Pivot Transformation|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 [[articles:SSIS: List of Transformations|this Wiki article]].