It's been a long time and I see that everyone is still using old SSIS packages from 2005 up to 2012, but I generally don't see new techniques or approaches being used. So, I decided to write a blog about it.
The blog mainly shows you how to create a new SSIS package and read CSV files from a folder "ToBeProcessed" and push the data into a SQL server and finally move the file to a backup folder, I even have provided the code just click here.
Most of the SSIS ETL designs that I see are a simple DFT that reads the file and does the magic, the problem is that you will need to fire off the SSIS package multiple times to load all the files. I don’t see a simple loop that loops through the files and processes them. The other thing I see is that within the SSIS the designers are applying the business logic and that is something that I don’t like, and I have had bad experiences with SSIS.
The most important part for me is that I have never seen a company dealing with hundreds of SSIS packages and having a SSIS framework. To me the most important things in SSIS are:
- SSIS Framework design
- Consistency
- Accuracy (using SSIS Framework design with .NET code)
- Simplicity
- SSIS Naming Convention
With the above approach I have designed an SSIS mapping tool that creates 100 SSIS package in less than 43 seconds.
Enjoy the code.
Reference
http://sqldataside.blogspot.ca/2018/04/looping-through-flat-files-using-ssis.html