Looping Through Flat Files Using SSIS 2016

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:

  1. SSIS Framework design
  2. Consistency
  3. Accuracy (using SSIS Framework design with .NET code)
  4. Simplicity
  5. 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.


Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now