Case Study
Here's the Case-Study of some of our works we'd like you to see...
About SSIS Case Study
SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data integration and data transformation tasks. It is an ETL tool part of the Microsoft Business Intelligence suite that is mainly used to achieve Data Integration.
Technology Stack
SQL Server Integration Services
Azure SQL Database
SQL Server 2017
C#
winrar
The Challenge
Recently, our former client came up with a query in which they wanted the zip file(s) for the fuel analysis data from various fuel branch units and organizations (who are their clients) to be loaded to their Pre-staging databases.
The challenges are each zip file had very bulky data which is around 3GB to be loaded to Presaging database taking around 7 hours to process. Here, the challenge was to reduce the process time for the entire Pre-staging database and make it quicker as much as possible. Initially, each zip file they had was filled with very bulky data of around 3GB and was taking around 10 to 11 hours to process in the Pre-staging databases.
The Solution
Initially, we reviewed their whole process. And by reviewing it, we found that they were using Python Scripts in the database. Then after, we started our execution. According to our prompt commitment, we dedicatedly discovered the solution on how we can reduce the Pre-staging database process time.
Beside this, we started conceptualizing and executing individual arrangements. We also implemented parallelism into it to load the data very quickly into the Pre-staging database. By doing this, we diminished the processing time from 10 - 11 hours to 1 hour but still we were not happy with that. So, we continued putting our efforts for reducing the time.
At last, we found a spectacular trick in SSIS using which we effectively decreased the processing time from 1 hour to only 30 minutes . It took approximately 1 week of time to accomplish our client’s desires.
MagnusMinds Team was victorious in decreasing the client Pre-staging database process. Initially, it was taking 10-11 hours of process time and now it is taking just 30 minutes of process time for the entire Pre-staging database.
Methodology:
- Download the zip files from SFTP.
- Extract file one by one and process it.
- File size was more than 3 GB. Previously it was taking 10 to 11 hours to process but after the implementation of ETL process it completed within 30 mins.
- We have designed ETL for this process.
SSIS Package:
- Download the file from SFTP.
- Extract the file and dump data into the SQL staging table.
- Process staging table data as per the business requirements.
- After processing the data, push that data into the actual table.
- Move extracted file to Archive folder.
- Send an email notification when SSIS Package got any error.