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