First, we will add a package to the existing SSIS solution. Then we will add a Flat File Connection Manager and point the pivoteddata.txt file to it. Finally, we will add a Flat file source and point the previously added Flat File Connection Manager to it. Below depicts the data output you should see from the Flat File Source.
Our next steps is to do the unpivoting. Unlike the pivot configuration, the unpivot configuration is relatively simple.
You will need to select the pivot columns, which in this case the pivot columns are Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec. The pivot column name will be in column titled Month, which you can configure at the bottom of the screen. The values of these pivotedkey columns (Jan, Feb etc.) into column called Qty which can be configured at destination column.
In this unpivot example there is a small issue. Here the columns which have a value of “0” in the Qty column will be transformed into rows. For example, the Qty of accessories in January of 2002 is ‘0’ and there will be and unnecessary row for that record.To alleviate these records, a conditional split with a simple condition is used. The condition is Qty > 0, which will eliminate any row with “0” in the Qty field.
Below is the output of the unpivoted transformation.
Below depicts the image of the final unpivoted package.
Key to Sample SSIS Package
|
Package |
Description |
|
PivotSampleForQuarter.dtsx |
Pivot data for One Year Quarter wise |
|
PivotSampleForMonths.dtsx |
Pivot data for One Year monthly wise |
|
PivotSampleForAllYears.dtsx |
Pivot data for all data including year |
|
UnpivotSample.dtsx |
UnPivot Data |
Conclusion
Pivot and unpivot transformations are important controls from which you can improve your data presentation. With minimum time and knowledge, you can successfully change your data representation.
]]>