SQL Server 2017 Hosting – 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.

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.