Fuel Analysis SSIS Case Study

Case Study

Here's the Case-Study of some of our works we'd like you to see...

case-study-about
Case Study

Fuel Analysis SSIS

Fixed the system of Predictive Intelligence that integrates internal data with the largest unified source of world trends and econometrics. It operates customized AI solutions and curates real-time structured and unstructured data.

Technology Stack

SSIS
SQL Server Integration Services
azure
Azure SQL Database
SQL
SQL Server 2017
C#
C#
winrar
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.

Here, the challenge was to reduce the processing 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.

case-study-the-challenge
case-study-about

The Solution

Initially, we reviewed their whole process. And by reviewing it, we found that they were using Python Scripts in the database. Then, 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.

Besides 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 in our efforts into 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.

case-study-conclusion

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 files one by one and process them.
  • The file size was more than 3 GB. Previously it was taking 10 to 11 hours to process but after the implementation of the 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 the extracted file to an Archive folder.
  • Send an email notification when SSIS Package got any error.
Loader