Had a request to load some prices into SQL Server from an excel spreadsheet - unfortunately the data was in a pivoted format without the raw pivot data.

This becomes the pivot description for the pivot value - in the example below I wanted to pivot on a variety of durations and get back the prices so set pivot key value to the duration.So having the spreadsheet as a source I created a table as the output - using the new table from oledb desination - modified the table name and we have a new table of the unpivoted data

Much easier if the spreadsheet contains the original unpivoted data - but this gets you the same results, for this client I also added another data transform task for data from another sheet in the same spreadsheet and finally a transform to merge the data from both sources into their data tables

Have fun with it - it's easier than loading the data in manually.