How and why I built ETL for Loading of data from text files and refreshing of reports using Loaded Files

Published Dec 01, 2020

About me

Database/Data Warehouse/SSIS Developer

The problem I wanted to solve

The problem was that we have to download csv files from Azure blob and based on these files have to refresh certain Power BI and Excel Reports.

What is ETL for Loading of data from text files and refreshing of reports using Loaded Files?

Tech stack

To do this I created a Staging Db and in Staging Db in one table I put the names of the Files that has to be loaded in DB. In Second table i put the names of the reports and stored procedure name that has to be executed if its triggers (Files required to refresh the report) is loaded in the DB. In third table i put the name of the CSV files against the Report ID. (Each report has different triggers File.)

The process of building ETL for Loading of data from text files and refreshing of reports using Loaded Files

I developed the ETL. ETL has two parts Part 1 is to load files into db and part 2 was to execute the report stored procedure if its files are loaded. To load a stored procedure for execution i created a View and write login in that view. (Which report has to be refreshed)

Challenges I faced

Developing a batch logic. (Which report has to be refreshed)

Key learnings

Learned and got expert in Configuration based SSIS ETL

Tips and advice

Develop an ETL that is configuration based like in that case if new files need to be added jus add the files in the db table and if a new report need to be added just add it in report table.