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
ASP.NET
SQL Server Reporting Services
Jquery
Javascript
The Challenge
Recently, our previous customer came with his problem. They receive the zip file(s) for the health care data from various medications 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.
The client's essential desire was to decrease the Pre-staging database process taking time and make it quicker as much as possible.
The Solution
First of all, we have checked the customer's whole process. They are using Python Scripts in the database. Afterward, we started execution on it. According to our prompt commitment, we have dedicatedly discovered the solution that how we can reduce the Pre-staging database process time.
Besides, we as a whole were starting conceptualizing and execute individually arrangement at long last. We diminished process taking time around 1 hour still we were not happy with that then we were continuously putting our efforts for reducing the time.
Finally, we found the spectacular trick using SSIS and then we got the way how we can reach our goal and effectively we decreased process timing in 10 minutes, which was a fabulous challenge for Team MagnusMinds. It took approximately one week of time for accomplish client desires.
We also implemented parallelism into it to load the data very quickly into the Pre staging database.
MagnusMinds Team was victorious in decreasing the client Pre-staging database process.
Previously, it was taking 7hrs process time.
Now, it’s taking a maximum of 10 Min of process time for the entire Pre-staging database
Overall, it performs each following step:
- It extracts the given zip file.
- Process the extracted files whether it’s a CSV flat file source one by one in for each loop container, the file extension is configurable parameter.
- It performs Upsert as the data is incremental.
- After loading the data of all files, it archives the processed files.
- The customer is using their own ETL framework, so during the package the details about.
- Batch, Batch set, task, package step managed for the internal SSIS ETL Framework database.
- It’s really a big package and many ifs and buts were elegantly handled in this package.