The SSIS script component rocks, don't you think? I think I might have made this point before on 4 or 5, possibly a few hundred, occasions. The ability it gives you to incorporate the power of the .Net framework and your own assemblies into the SSIS pipeline can't be overstated - it puts SSIS head and shoulders above any other enterprise ETL tool in terms of breadth of functionality.
There's one problem with the script component though. The only way to reuse your script components in different packages is the most simplest of all methods of code reuse - copy and paste*. Now copy and paste is OK but I don't think I need to list here the obvious drawbacks of doing that. For true reusability you really have to be building your own custom .Net components.
Then, on the train home tonight as I was reading Donald Farmer's Rational Guide To Scripting SQL Server 2005 Integration Services a lightbulb pinged on. And its simply an extension of an idea I've already had. Here's a way of sharing script component functionality around different places via a modular, distributable, component. Let me explain.
If you read this blog regularly you'll know that one of my big hopes for the next version of SSIS is that there is more provision for the reuse of tasks and components. I have blogged previously (http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx) about an idea I had for building custom data-flow components without having to write even a scrap of code. The idea is basically to fashion new components out of groups of existing pre-configured ones and make that available as a distributable component in the normal way. Well take the idea a bit further. Suppose that our group of components is actually just one component. And suppose that that component is a script component. If we can package that up into a distributable component in the way that I just described then there we have it - a distributable script component containing custom functionality. All without having to go through the much more difficult process of building a custom .Net component. The person using it need not even know that under the covers its a script component - they don't care. All they see is a component in their toolbox with inputs and outputs and that does a certain job.
Comments anyone? I think the potential for improving the product in this way is fantastic and I'm pleased to say that there have already been murmerings in the SSIS dev team of implementing functionality just like this. Here's hoping...
-Jamie
*For a discussion of code reuse in SSIS see Kristian Wedberg's article here: http://www.sqlservercentral.com/columnists/kwedberg/ssiscodereuseandcomplexcontrolflows.asp
**Donald Farmer's book "Rational Guide To Scripting SQL Server 2005 Integration Services" is available now!
