Case Study

ETL Development Leads to Efficiencies for This Food Distributor

Technologies Supported: Azure SQL Server SSIS

A security scare pushed this food distributor to rethink its entire data environment. IncWorx moved its SQL Server footprint to Azure and rebuilt the SSIS packages behind it, turning an aging, unsupported setup into a secure platform ready to grow.

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

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