Should programmers use SSIS, and if so, why?
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.
Answer
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