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:
- SSIS Framework design
- Accuracy (using SSIS Framework design with .NET code)
- SSIS Naming Convention
With the above approach I have designed an SSIS mapping tool that creates 100 SSIS package in less than 43 seconds.