Data Analysis Transformations in SSIS 2012 2008 

1. Pivot Transformation
2. Unpivot Transformation
3. Data Query Transformation

1. The Pivot transformation takes data from a normalised result set and presents the data in a cross tabulated or denormalised structure. 
2. For example, a normalised Orders data set that lists customer name, product and quantity purchased typically has multiple rows for any customer who purchased multiple products, with each row for that customer showing order details for a different product. 
3. By pivoting the data set on the product column, the Pivot transformation can output a data set with a single row per customer. 
4. That single row lists all the purchases by the customer, with the product names shown as column names, and the quantity shown as a value in the product column.
5.  Because not every customer purchases every product, many columns may contain null values.

• Component Properties tab. The Component Properties tab allows you to specify a Name and Description for the OLE DB Command Task. You can also specify a locale with the LocaleID property and whether the data flow is validated at run time or design time by using the ValidateExternalMetadata property.
• Input Columns tab. The Input Columns tab consists of the Available Input Columns property that enables you to select the columns that the Pivot transformation operation will affect. When a column is selected, it appears in the Input Columns list. The Output alias allows you to define the name of the output column. The Usage Type property defines if the data imported is READONLY data or READWRITE data.
• Input and Output Properties tab. The Input and Output Properties tab allows you to set the properties of the input and the output columns. The most important property here is the PivotUsage property. This determines what role the input column will play in creating the pivot table and can be configured with the following values:
• 0. The column is not pivoted, and the values are passed through to the transformation output.
• 1. The column is part of the set key that identifies one or more rows as part of one set.
• 2. The column is a pivot column. At least one column is created from each column value. This data must be sorted input column.
• 3. The values from this column are placed in columns that are created because of the pivot.

Unpivot transformation

1. The Unpivot transformation takes data from a denormalised or cross-tabulated result set and presents the data in a normalised structure. 
2. The Unpivot transformation can be configured with the following properties.
3. At the bottom of the Unpivot Transformation Editor dialog box is the Pivot key value column name. 
4. Here, you define a column heading for the column that will hold the pivoted data that is converted into normalised data such as Products or Fruits.
5. The Available Input Columns property enables you to select the input columns that the Unpivot transformation operation turns into rows. 
6. When a column is selected, it appears in the Input Columns list. Any columns that are not selected are passed through to the data flow output. The Destination Column allows you to define the name of the destination column in the normalised output.
7. In the Unpivot scenario, multiple input columns are usually mapped to one destination column. 
8. For example, the Available Input Columns may consist of column headings such as Apples, Pears and Peaches.
9.  All of these input columns are mapped to a destination column named Fruits that may be defined by the Pivot key value column name property.
10. The Pivot Key value property specifies the value that is used in the rows in the normalised result set and, by default, uses the same name as the input column but can be changed.