Case Studies
ETL Development Leads to Efficiencies for This Food Distributor
This food distribution customer was in need of optimization and growth of their SSIS footprint to improve operational efficiencies and business process. The experts at IncWorx provided the secure environment and ETL development skills necessary to reduce costs, grow distribution channels and improve employee processes.
Technologies Supported
- Azure
- SQL Server
- SQL Server Integration Services (SSIS)
The Challenge
A Need for Operational Efficiencies and Process Improvements
After a recent security event, this food distribution customer needed assistance moving its entire on-premises database footprint to Microsoft Azure. In addition to the secure build-out, they wanted to look for operational efficiencies and process improvement in their existing SQL Server Integration Services (SSIS) packages. These packages had been developed years prior for their ERP and sales database system and were not current. The customer lacked internal expertise and was in search of ETL consultants to help.
The customer provided some key requirements, which included:
- Provide Microsoft Security expertise
- Migrate SQL Server to Azure
- Analyze existing SSIS footprint
- Grow the SSIS footprint with ETL development expertise
The Solution
Build Better Security and Data Infrastructure
On day one, our team of SQL Server experts became members of the customer’s existing Scrum teams. Adding this expertise meant the Scrum teams could make better decisions in relation to security, database maintenance and business process improvement.
Given a recent critical security incident, it was important to prioritize architecting a data platform that was security-hardened and followed all current SQL Server security best practices. To reduce the risk of encountering another security issue, the customer decided to migrate the on-premises SQL Server to Azure. A development environment was moved first followed by their production environment.
After the migration to Azure, we went to work reviewing their existing SSIS files. Following our extract-transform-load (ETL) audit process, we were able to uncover several small but significant inefficiencies that were holding back multiple teams including their sales team. Many of the existing packages were optimized in several situations, we created new packages that provided additional insight into data loaded from the ERP and sales systems.
Key areas of focus for this project included:
- Expanding expertise of internal Scrum teams with IncWorx SQL expertise
- Guidance on all security for new production and development environments
- Installation and configuration of SQL Server in Azure, following industry best practices
- Migration of production and development database environments to Azure
- Analysis of existing SSIS packages for inefficiencies and process improvements
- Development of new SSIS packages to load data from ERP systems to SQL Server
- Creation of new stored procedures and views, as well as new tables and schemas
The Outcome
Reduced Costs, Growing Distribution Channels, and Process Improvements
The project took several months but ultimately gave this customer confidence in the security of their newly built Azure SQL Server environment. The improvements and additions made to their SSIS packages provided data insights that would allow them to reduce costs, grow distribution channels and improve employee processes.
The Conclusion
A Secure and Robust Data Processing Platform
The Microsoft technology stack provides the infrastructure and software necessary to maintain and grow your business. Combining Azure, SQL Server databases and SQL Server Integration Services packages provided this customer with a secure and robust data processing platform that will ensure the growth of their business for years to come. Do not delay your opportunity to get more from your data with ETL development.