I had a recent requirement to integrate multi-language support into a SQL DW via a SQL SSIS ETL solution. Specifically the SQL DW platform currently only supported English translation data for all Dimension tables, but the business was expanding internationally so there was a need to include other language translations of the Dimensional attributes.
We wanted to do this without having to manually translate English text attributes that exist already, or new ones that are added or modified over time. We wanted an automated method that simply “worked“.
Enter Azure Cognitive Services Translator Text API service!
So the purpose of this blog is to outline the code/pattern we used to integrate the Azure Cognitive Services API into SQL SSIS ETL packages.