I've been working with SQL Server Integration Services (SSIS) for quiet some time now and as time goes on more and more nice-to-have's occur to me.
I've realised that many operations that we are going to use SSIS for can be leveraged in many situations and that gives rise to the concept of modularity and reusability. SSIS supports reusability by allowing us to encapsulate functionality into a single executable -a package- and then being able to execute it from another package. Typical applications of this may be:
- A parameterised logging package
- Fetching oft-required data into a variable
- A parameterised sendmail package
This is good stuff and is very useful however I don't think it goes far enough. As I have said previously it would be a useful if the unit of reusability was more granular than a package, for example a data-flow, a pre-configured control-flow task or even a pre-configured data-flow component. For example, if we had a pre-configured sendmail task that we could share throughout all our packages and (crucially) modify in only 1 place we would save the overhead of having to call out to another package.
Recently it occurred to me that it would also be useful to reuse a collection of data-flow components that collectively form a common task. If that reusable component could be shared throughout different data-flows just as if it were a component all of its own - that would be incredibly useful. For a start it would save on building custom components in .Net to accomplish tasks that the provided components just can't do.
I'm pretty darned useless at describing these sorts of things so perhaps an example would be more useful. Imagine a scenario where we want to process IIS web log files. We would have to use a flat file source component to source the data and a derived column component to split the file contents into each requisite column. That's 2 components that we may be rebuilding many times and that, to me, seems like unnecassary work.
Douglas Mcdowell and Jay Hackney recently wrote an article that described how to write a custom source adapter for an IIS web log. Its a very useful bit of code. and I urge you to look at it. Mind you, I don't like writing code - I'd rather use the components that I already have available to me, namely the flat file source and the derived column components as I described above.
That's when the light bulb flicked on above my head. If SSIS had the ability to produce a new component that is an amalgamation of these 2 already configured components, my job is done. I have a brand new component that I can pick up and share throughout my packages as if it were a custom component that I had written in code. Essentially I want to be able to build custom components using the SSIS designer rather than writing them in .Net. That would be wonderfully powerful functionality
I hope I've successfully managed to map out my thoughts on this. In my opinion the ability to do this would add greatly to the overall ease-of-use and ease-of-extensibility of SSIS!
-Jamie