Should programmers use SSIS, and if so, why? [closed]
As a .NET developer, for what reasons should I prefer SSIS packages over writing code? We have a ton of packages in production where I currently work, and they're a nightmare to both "write" (perhaps draw?) and maintain. Each package looks like a bowl of multicolored spaghetti with C# and VB.NET scripts mixed in at the points where the abstractions break down. To figure out what each "Execute SQL Task" or "Foreach Loop" does, I have to double click the damned thing and browse through a tree of literal values and expressions, scattered across multiple tabs.
I'm open minded, so I'd like to know if any other good developers find SSIS more productive than just writing some code. If you do find SSIS more productive, please tell me why.
I use SSIS every day to maintain and manage a large data warehouse and cube. I have been 100% business intelligence and data warehousing for two years. Before that I was a .NET application developer for 10.
The value of SSIS is as a workflow engine to move data from one spot to another with maybe some limited transformation and conditional branching along the way. If your packages contain a lot of script then your team is using SSIS for the wrong tasks or isn't comfortable with SQL or has bought into the hype. SSIS packages are very difficult to debug. Script components are an absolute nightmare and should be used only for formatting, looping, or as a last resort.
- Keep your packages simple, sql tasks and data flow tasks.
- Do as much work as possible outside of SSIS, preferably in SQL
- Keep your variables in a single global scope
- Keep your SQL in variables or store procedures, never in-line
- Keep your variable values in a configuration store, preferably a SQL database
I tried using SSIS several times, and gave up on it. IMO it is much easier to just do all I need in C#. SSIS is too complex, it has too many gotchas, and it is just not worth it. It is much better to spend more time on improving C# skills than to spend same time on learning SSIS - you'll get much more return on your training.
Also finding and maintaining functionality in a VS solution is so very much easier. Unit testing with VS is easy. All I need to do is to check in the source in Subversion, and verify how it loaded. Unit testing SSIS packages is very involved to put it mildly.
Besides, there were situations when SSIS was silently failing to populate some columns in some rows, just skipping them without raising exceptions. We spent a lot of time troubleshooting and figuring out what is going on. Developing an alternative solution in C# took less than an hour, and works without any problems for two years.
In my opinion - SSIS is for ETL operations only and should contain no logic outside that scope.