Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Reusing script components

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!

Published Wednesday, September 07, 2005 7:47 PM by jamie.thomson

Comments

 

Ivan Peev said:

Hello Guys,

I just wanted to comment we have implemented an extension of the standard Microsoft Script Task. It allows the implementation of your own script's user interface and has better script reusability. This can can be used as alternative to implementing a full blown custom SSIS control flow task. For more information please visit: http://www.cozyroc.com/products.html

Regards,

Ivan

July 8, 2007 6:03 AM
 

Ivan Peev said:

We have implemented another reusability extension, this time for the standard Data Flow task. You can now export data flow logic and reuse it in other packages without a need to copy-and-paste. You can also implement your own setup UI for your data flow with SSIS script.

You may check our blog about it here: http://cozyroc.wordpress.com/2007/12/17/cozyroc-ssis-12-beta-2-released/

and download it from here:

http://www.cozyroc.com

December 21, 2007 2:18 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems