I am practicing SSIS and currently working on Pivot transformation. Here's what i am working on. I created a Data Source (Table name: Pivot) with the following data.
Using SSIS, i created a package for Pivoting the data to have the following columns
PersonID --- Product1 --- Product2 --- Product3.
Here's where am at, I was able to create the pivot data to text file. But The output is not grouped by PersonID.
My Current Output is
As we can see the Transformation does not group the based on SetKey(PersonID : PivotUsage =1)
The output i am hoping to get is
Where the data is grouped based on PersonID.
What am i missing here?
Edit: Going back to the example i was following, I re-ordered the input data as follows.
Does the Input data need to be in this order/pattern, every time? Most of the examples i came across follow the similar pattern.
Yes, the input data needs to be sorted by whatever you're pivoting on:
That's a direct quote from the Pivot Transformation documentation on MSDN. (Emphasis added.)