Pivot and UnPivot with SSIS

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.

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |