Problem Statement:
To provide information on Shipped and Non-Shipped Orders from the list of orders dropped by clients in SFTP servers.
GitHub
Tools and Technologies:
- Azure Data Factory, SQL Server Management Studio, Logic App, Azure SQL, Azure Key Vault
Summary:
- XML files were dropped by our client on SFTP servers Country-wise which contained multiple Order Numbers. They wanted to know how many orders have been shipped from the Business Central (BC) side.
- Picked those files from SFTP servers using Azure Data Factory (ADF) and saved them in a temp SQL table.
- All shipments’ information is saved on Azure SQL in the XML Column of the Table.
- Used stored procedures and temp tables to get matched order numbers and then their shipments.
- Traversed the XML Column in SQL to find all the shipments for a specific order and created one Shipment XML file for each Website.
- Sent all Files to their SFTP servers respective to their websites.
- Triggered the pipeline to work Daily.