create custom scalar functions for use in SSIS

[ddiamond]

I'm attempting to re-write an ETL solution with SSIS that was originally written using MS Access 97. The Access 97 solution contains a lot of custom VBA scalar functions that encapsulate business logic. These scalar functions are used to create derived columns in many different queries. The idea is that when the business logic changes, we simply modify the VBA function rather than modifying each query that calls the function.

For the new SSIS solution, it would make sense to use a derived column transformation that references a .net (c#) function in the derived column expression. But this will only work if I can share the .net function across multiple packages. Is that possible? For example, if I compiled all of the custom functions into a dll, would it be possible for the packages to reference that dll?

Using a scripting component isn't an option because I want to be able to reuse the code in multiple packages, and I don't want to have to update each package when the business rules change.

 

[RiverGuy]

I've only ever created them with script tasks and simply copied the script tasks to new packages, but here is an article detailing how to compile one to add to the toolbox:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/63877/