Unpivot transformation with SQL server 2005 Integration Services
During the past week I played a little bit with SQL Server 2005 Integration Services in order to understand some basic concepts. As usually I do, I start with some 'real' cases I have in other contexts and try to understand how to do with the new technology. The problem was pretty simple, normalize a denormalized flat file. Consider a flat file containing the following data (content is fictious):
Date;USD;EUR;JPY
12/12/2004;1.2332;1;23.32
I have to create another flat file (just as sample) like following:
date;Currency;Price
12/12/2004;USD;1.2332
12/12/2004;EUR;1
12/12/2004;JPY;23.32
So, I created one flat file source and one flat file destination with the structire as above. Then I placed an Unpivot Transformation component linked to the flat file components with the following settings:
- Input Column: USD, EUR, JPY, ... (all columns except Date)
- Data Column: Price (all the same)
- Pivot Value as Input Columns
- Pivot value column name: Currency
Run the package and everything works fine. I have to thank Ashvini Sharma for his valuable help.