SSIS Pivot Transformation

SSIS Pivot Transformation

Integration Services uses transformations to manipulate data during an ETL dataflow. Transformations can be used in [[articles:SSIS Dataflow Task|Data Flow Tasks]], between data source and destination components, or other transformations.

There are many cases (for example, in reporting), when some elements of a dataset has to be transposed. The Pivot transformation can help in this during the load process of a data warehouse.

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




Then, with using the Pivot transformation, it can be transposed like this:


The transformation does not have a simple editor. It can be configured in the Advanced Editor of the transformation.
In this case, in the Input Columns page of the Advanced Editor, all columns had been checked in:


On the Input and Output Properties page, the PivotUsage of all input columns had been changed: 1 for Period, 2 for Metric Name and 3 for Amount. The options are the following:
All output columns must be configured and mapped to one of the original columns manually. This can be done by setting the PivotKeyValue and the SourceColumn property of all new columns:
In this case, for the new column called Profit, the PivotKey value needs to be Profit, and the SourceColumn value is 111, which is the LineageID value of the Amount input column:


 This transformation supports an input, a regular and an error output. It looks like this in the data flow editor of BIDS (PVT Pivot):



This is a partially blocking transformation because of the introduction of completely new buffers.

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

So, the Pivot transformation can be used as an opposite transformation of the Unpivot transformation, which is helpful when a more normalized 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.


Other Languages

This article is also available in the following languages:

 

Sort by: Published Date | Most Recent | Most Useful
Comments
  • This page should be updated to SQL 2012 ASAP.

  • Zoltán Horváth edited Revision 1. Comment: adding Other Languages, tagging

Page 1 of 1 (2 items)