A customer currently generates XML files from operating devices and wish to gain insight into the data they are generating. They require that the data is transformed and landed in an Azure SQL Database and PowerBI visualizations. At present this data resides within the customers own network, so to overcome this the Data Management Gateway is used.
Data Management Gateway provides an agent that is installed in the on-premises environment to copy data between on-premises data stores and the cloud. Azure Data Factory was chosen as the mechanism to orchestrate the ingestion of XML files, additionally Azure Data Lake Analytics was chosen as a mechanism to transform XML to CSV to enable the data to be landed in an Azure SQL Database. A specific requirement involved the need to validate the XML against an XSD schema, this capability does not exist in Data Factory. A Custom .NET Activity easily allows us to write custom code to achieve these ends.
Overall it becomes a simple exercise to ingest data from on premises data sources, to write custom activities for Azure Data Factory, and to land the data in Azure Data Analytics Store, to then be consumed by Azure Data Lake Analytics to extract the content from the XML Files. Subsequent copy activities will land the data into an Azure SQL Database.
