In this post I’m going to show how we re-import our completed report back to our SQL Server table using the SSIS Unpivot component.
So our content team, having inserted the missing translations (except Portuguese) into our report, want to see the new translations on the website. Here’s how we load the table…
Firstly, we select all columns from the report in our Excel Source component.
Then we drag the Unpivot component on to our workspace. Thankfully configuring this isn’t as difficult as the pivot component… However, we still need to be aware what role each column performs.
I will start with what I call the pivot anchor (VehicleTypeId). This is always the easiest one to decide, as no transformation needs to occur on this. Therefore we can check the pass through box in order that this column flows through to the downstream component.
Next we will turn our attention to the column headings / pivot keys (Language). These will be assigned a value in line with what we want to appear in the normalised table. This value will be inserted into our LanguagesId column, so we can map the input columns on the left of the component editor, to a pivot key value (English 1, Spanish 2, French 3, German 4, Italian 5, Dutch 6 and Portuguese 7). Note that there is a field on the bottom right of the component editor with the label “Pivot key value column name”. This will become the output column, which holds the mapped report headers in the dataflow.
Finally, we need to decide on a column name to hold the translations themselves. We specify this in the second column of the component editor. I have used “VehicleTypeName” as per the table in our DB.
Now that we have configure the Unpivot component we are outputting 3 columns to the pipeline.
The next downstream transformation is a data conversion to cast LanguageId and VehicleTypeId to INTEGERs as required by the table.
Then the data flows into a lookup component – we use this to see if the record already exists. We had a number of translations to begin with, so using the natural key of LanguageId and VehicleTypeId, we join to the VehicleTypeTranslation table.
If a record fails, then it is a new translation and therefore goes to the insert component (you can see that we had 10 new translations in our report and therefore 10 new records inserted), if it succeeds then we update the translation that is already there.
And that is it! Please leave any feedback you have on this article. I am never sure how clear my ramblings are 🙂