SSIS Unpivot

In this post, let us see how to unpivot data using SSIS. Let us consider below sample data




Below screenshot shows the above data in unpivoted format:









In this example, data from flat file source will be unpivoted and stored in SQL Server with below table definition:

SSIS package, data flow will consist of Flat file source -> Unpivot transformation -> OLE DB destination



We need to ensure all the columns that are getting unpivoted need to have same data type, otherwise it will throw metadata mismatch error.

We can keep same data types for all unpivoted columns, by right-clicking on Source componenet -> Advanced Properties -> Input and Output Properties -> Output columns -> Data Type



Below screenshot show's unpivot transformation settings:

Values in the column Mobile, Laptop, TV & Other Electronic device goes into SALES column
and column headers Mobile, Laptop, TV & Other Electronic device goes into Product column.


5 rows got flattened into 20 rows after unpivoting. 




See Also:



    No comments: