Skip to Content

Case Study

ETL Development Leads to Efficiencies for This Food Distributor
Technologies Supported: Azure SQL Server SSIS
Woman pushing a shopping cart through a grocery store produce aisle

A security scare pushed this food distributor to rebuild its data environment. IncWorx moved it to Azure and rebuilt SSIS from scratch.

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
Woman at a desk reaching for paperwork among files and notebooks

The Results

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 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.

Application Lifecycle Management for Manufacturing

Read

Migration Playbook Key To Online Retailers SQL Server Upgrade

Read

Technologies

Let's Build a Data Platform That Works for You

Azure, SQL Server, and SSIS done right give your team room to grow instead of workarounds to manage. Get more from your data with ETL development built for where your business is headed.

Let's Get Started