Hi I work with a .net team where the leadership has decided to use SSIS for ALL of backend processing, and that is the only option given to us.  I have pointed out the following issues with using SSIS this way to the leaders but they are disregarding these obvious flaws with this approach.  Could you please provide your expert thoughts on this, so I can share with them and perhaps try to persuade them out of this nightmare approach to development, and/or also do point out if I am wrong on this.  Big Thanks.

Issues I pointed out:

- SSIS is SQL centric technologies ( for mostly fast data transport and some transformation) and should not be over extended to write an entire enterprise transactional system that requires GUI's to maintain and has complex rules.

- You can cannot create reusable components/objects with SSIS, again because that is not what is meant for.

- They are running SSIS on app server (separate from SQL server) which requires additional SQL license.

- It is hard to debug (VS2005) because it does not have the same full fledge debugging and enhancement tools that you get with .net application using visual studio.

- You cannot do text compare against your source control.

- It is very hard to develop/maintain complex rules (which we have plenty of) using only SSIS.

- You cannot build GUI on top of SSIS and reuse anything for the GUI (which our support team is always asking for)

I have many more reasons but I think the above should get my point across.

Thanks again.

Please elaborate what do you mean when saying "build backend enterprise system". You have stated issues, but you have not provided details why your leadership thinks SSIS is the right tool. May I ask what would be the better approach if SSIS is not the answer/solution?

Btw some of the issues you have mentioned are either already solved or there is a third-party solution available.

There are a large flat transactional files coming into our system, that require complex rules processing and another flat file is produced that is fed into our system.   Processing is time sensitive and all files must processed with certain amount of hours daily.

The leadership always uses SSIS and they always blindly think it is right tool for any problem (essentially that is my problem with them) . Trust me, I am not exaggerating. 

Here is what they are proposing: lets say SYSTEM "A" = Source system, SYSTEM "B" = destination system.

SSIS Package 1:They have a SSIS package to read the files from system A and do some minor filtering and then produce another file in the same System A format.  Script task is used to parse out the field values.  (My issue here: file parsing is contained within the script task which means I have to write/copy the same code in other packages where it will be needed, which makes the code hard to maintain and increases QC time)

SSIS Package 2: Reads the file produced by Package one, retrieves data from DB into the package, compares data coming from the file against data read from DB against complex rules and produces a flat file in the format of SYSTEM B.  (My issue here: complex rules are very hard to debug in SSIS, product development wants to track all the rules applied and in case of failure they want to pin point where it happened and why.  They also at some point to build a GUI on top of this.)

They'll have a parent package to run multiple instances of the above two packages.

My proposal: .net Object Oriented (NO-SSIS)

- Create readers and writer objects for SYSTEM A file format and SYSTEM B file format, so they can be consumed by different types of applications (processing apps, GUI's etc.)  They can also be QC'd independently of the applications.

- .net App Reads files from SYSTEM A and Retrieves data from DB applies rules and produces file in SYSTEM B format.  All of this happens in cached fashion lets say 50000 records at a time so the memory doesn't get blown up.

- Different instance of the code can be run or multi-threaded, to simultaneously processes more than one file.

- Custom Error/Logging system can be hooked to the processing engine for tracking purposes.

- processing engine can also be QC'd independently of the application, (as I would put that in its own dll just as the readers and writers)

Thanks for the response.

The solution you propose has one major flow: it requires developer to maintain and fix. Another issue with the solution you propose is that initially it may look handy and simple , but once the requirements start to grow, your "simple solution" will have to acquire more and more features from what a good ETL framework has to offer. And that's when the real pain will start and it is all downhill from there.

The scenario you describe is actually very close to what SSIS was designed to be - data processing, integration and transformation framework. If you have specific functionality you want to use in multiple places, you just implement a reusable component for it. That's how you get the best of both worlds. Not by implementing everything from scratch yourself.