SQL Server 2005 SSIS Learning Curve Considered Steep

One man's opinion, and subject to change:

If you plan to build a system of "interesting complexity" using the new SQL Server Integration Services (SSIS) as a platform, plan ahead for a lot of extra implementation time that will be spent learning how this very new tool works.

When I say SSIS is "new" I do not mean to suggest that it is buggy (I have run into a few bugs, but only minor ones), but rather that the *thinking* required is new. If you're estimating an SSIS project that will involve multiple packages or a good amount of complex logic, leave yourself a lot of contingency in your schedule.

While SSIS does have some powerful logic and control-of-flow capabilities, it's sweet spot appears to be moving data around, and doing cool transforms, splits, pivots, and lookups along the way. Myself, I am finally becoming somewhat productive, but an entire day can go by trying to figure out how to get just one cluster of control of flow containers and tasks working.

When the logic gets past a certain level of complexity (or when a problem does not fit into the particular SSIS paradigm), then you have to supplement with code, either externally, or in a script task. You may find yourself working in code to set variables and data structures up in a particular way to make the tasks work the way you want.

Part of the challenge is learning what logic to keep *out of* SSIS and in external T-SQL or .NET code. Part of the challenge is finding just the right combination of containers, tasks, and precedence constraints to achieve what you want without making a mess of nested elements and criss-crossing lines. Part of the challenge is simply discovering the many little "gotchas" that any tool of this complexity will have.

I am not saying that SSIS is a poor tool (it's too early for me to conclude that, and my opinion at this point is still generally positive). It's a leap forward in capability, power, and, it appears, performance; and I still feel that in the end we're going to find that building our system in SSIS was a good decision. My main point is that the learning curve can be steep if your SSIS-based system will be at all complex. Plan for it.

Dan