none
Reusability in SSIS? RRS feed

  • Question

  • Something in another thread spurred me to post about this, wondering if others have solved this, or what workarounds they might be using.

    Suppose we want to do the same operation on more than one column, or the same data transformation in more than one data flow, or in more than one package.

    I don't know how to reuse anything in SSIS, except by copying & pasting and creating a second (or third, ...) copy.

    I'm reluctant to copy & paste expressions or boxes or entire data flows, because (a) any change has to be made multiple times, and (b) if someone later changes one copy without realizing that there are other copies, they might go out of sync (I've seen this happen and lead to time-consuming debugging sessions).

    The only answer that I've figured out is to push operations out of SSIS into the T-SQL world, where reusability is easy (and familiar) -- make UDFs, views, sprocs to do operations, and call them from multiple locations in the SSIS packages.

    However, I don't know how to reuse expression operations in this fashion, because apparently I cannot call UDFs from Derived Column Expressions.

    I suppose another solution, at least for little items such as expressions, would be to make changes across all versions by doing automated search & replace operations across all the dtsx files -- I've not done this, and am a little hesitant to do automated changes to dtsx files. Is anyone doing this happily?

    Thursday, September 1, 2005 8:46 PM

All replies

  • Perry,
    I certainly wouldn't want to do automated changes to the XML in a .dtsx file - I reckon that's fraught with danger.

    At the moment the only unit of reusability is a package. In other words, seperate operations that you want to do more than once into a seperate package and call it using the Execute Package Task. You can't even call a data-flow from multiple points in the same package which really frustrates me - I don't think it would be that hard to implement.

    Reusability is an area in which MS need to make giant strides in vNext and I'm quite sure they're aware of this. I dare say its high priority in the features list for vNext. Its certainly something I keep bringing up and I'm sure Kirk Haselden is fed up of me banging on about it by now :)

    I have had some ideas about how reusability might be accomplished. My main idea is that, just as packages can be saved as .dtsx files, how about saving pre-configured tasks/components (e.g. A derived column transform that parses a date from a web log file or a Web Service Task that connects to a given web service) as .dtsx files (or perhaps .taskx/.comx files or something) and then being able to use them in a package just as you would a regular task or component. Now the unit of reusability is a task or a component instead of just a package. These pre-packaged tasks/components could be left as files or deployed up to a server just as you do with a package. If you could make them appear in the BIDS toolbox - so much the better!!!

    Pre-configured expressions is an interesting subject and I envisage that this could be done in a similar way.

    The other really compelling method of reusability would be the ability to build brand new custom data-flow components from existing pre-configured components without writing any code. For example I have a text file source adapter pointing at a web log file and a derived column transform that parses that web log file. Imagine if I could select those 2 components, right-click and select something like "Build new component" and it goes away and builds me a brand new distributable custom source adapter that does exactly the same as what those 2 components do together. This is something I've previously talked about here: http://blogs.conchango.com/jamiethomson/archive/2005/05/26/1470.aspx and is something that really really excites me - if its something that MS can achieve!!!

    I've wittered on a bit here...I'll stop now :)

    -Jamie
    Friday, September 2, 2005 8:32 AM
    Moderator
  • 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
    Saturday, July 14, 2007 9:01 PM
  • 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

    Friday, December 21, 2007 4:14 PM