RealTime-Vehicle-Tracking

Case Study

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

case-study-about

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

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.

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.

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

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