It may be me, but as yet I haven’t seen too much written about the SSIS pivot and unpivot components.
Whilst they are not the easiest to configure, when faced with the option of importing to SQL Server and using a UNION or the new PIVOT clauses, I actually prefer the SSIS component.
Rather than actually explain the component, I thought it might be useful to go through a real example, so here goes…
My client has a fairly large team dedicated to translating text into various European languages. My clients website uses many tables to hold these translations so that the right one can be chosen for the end user based on the domain they are visiting or the language they’ve chosen.
The Content Management System (CMS) system doesn’t have the complete functionality yet to allow updates to every single table and so we are left with the task of exporting the current translations we have into a spreadsheet and the content team fill in the blanks before we import it back in.
Here’s how I’ve set up an example package…
Firstly, we have the unpivoted/normalised table (VehicleTypeTranslation), the columns VehicleTypeTranslationId (primary key), VehicleTypeId, LanguageId and VehicleTypeName populated with English and partially populated with various other translations.
Next job is to pivot this into a report that can be easily read by our translators, so they can see whats missing from the table.
The source is an ordinary OLE DB source, but we are only selecting from 3 columns (VehicleTypeId, LanguageId and VehicleTypeName. The primary key is not useful in the report as firstly, it holds no meaning for the translators, and secondly being an Identity column it may be different in another environment (We will use the natural compound key of VehcileTypeId and LanguageId to reimport the translations later).
This is where it gets slightly interesting. The pivot component itself.
All the columns we have selected in the upstream component have a role in the pivot. The VehicleTypeId will have a PivotUsage of 1 – this is the anchor (the list down the left side of the report). LanguagesId will have a PivotUsage of 2 – this is the pivot key (all distinct values in this field map to the column headers in the pivoted table). Finally VehicleTypeName has a PivotUsage of 3 – this is the pivot value. Note, that a pivot usage of 0 means that a column does not partake in the pivot.
Having ascertained which column is used for what we can now begin to configure our component.
When you start off in the “Input And Output Properties” tab, you will find that if you highlight the input columns, the PivotUsage property needs configuring for each one. Set these according to the usage described above. Take note while you are doing this of the value of the LineageId property (used to map to output) for each input.
Next, you will need to add output columns. These will be the column headers of you output report and should therefore have a meaningful name. There is a caveat here though – unlike a SQL union statement where you can just report the pivot key you are interested in, with the SSIS component, you must at least (you can have more) have an output for each distinct value within the key column. i.e. In our example, we have 6 distinct LanguagesIds in our VehicleTypeTranslation table, so I will add an output column for each one, but I will also add one for Portugal which will be a brand new language as yet not present in the table. We also need a column to hold our pivot anchor, so I have created VehicleTypeId for this one too.
Once all the columns have been added, we need to map our input columns to the output columns. To do this you need to highlight your input column and check what the property value is for it’s LineageId. You will set the SourceColumn property of the output columns using this value.
In our example, the easiest one to start with is the Anchor(VehicleTypeId) column. This has a pivot usage 1 and will map directly to output column VehicleTypeId. Next we need to set the SourceColumn property for each of our language outputs. We will set these to the LineageId of the “pivot value” input column holding our translations (VehicleTypeName).
The final thing to do to is set the Pivot Key values for each output language. In other words what value is it looking for in our pivot key field in the normalised table in order to decide what column it belongs to in our denormalised report. This can be set according to what we know the LanguageIds to be. In this case English 1, Spanish 2, French 3, German 4, Italian 5, Dutch 6 & Portuguese 7.
Now to complete the dataflow we add an excel destination and output the data to Translation Report. This is what you end up with.
I’ll come on to the unpivot next time…