SSIS and re-using C#

Question

Hi, I'm a newbie to SSIS / C# (I'm generally a Java developer) so apologies if this is a really stupid question.

Essentially the problem is this: I have two Data Flow tasks which load data up and export them to a legacy flat file format. The formatting is done by a Script Task (C#).

What I'd like to do is share some common code between the two. e.g. I could create a common base class and then extend it for my two different script tasks.

However it seems that SSIS doesn't really make provision for this.

Does anyone know if there is a way of accomplishing what I want to do?

Thanks!

Answer 1

I am pretty sure it's possible to access .NET assemblies in SSIS scripts. So you could do it this way. See this article:

http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/3180/

Answer 2

I believe you will have to create an assembly or webservice for this to work.

Answer 3

You're correct that there is not a straightforward way to do this directly from SSIS.

In a recent project, we took two different approaches, which both worked fairly well depending on what you need to do:

1. Create a utility class (as a simple class library) and reference it from your script tasks. This is done pretty much the same as any other sort of reference. If you use .NET 3.5, remember that you'll have to update the version manually in the script tasks since SSIS defaults to 2.0. We also found that if we wanted some manner of reusability in the utility assembly (not relying on hardcoded variable names, etc.) then the package still had to have a fairly large amount of "setup" boilerplate to use the utility scripts.

2. Create a custom data flow component. This is a much more involved process, but ultimately will do the best in terms of avoiding code duplication. Generally, coding the actual data flow is fairly simple and not that much different than a script component, but the various setup code you'll need can tend to make things complicated. There's also not a lot of support in SSIS for when something goes wrong. Led to a lot of detective work on our project.

If you plan on using something a whole lot, and are committed to getting rid of boilerplate code as much as possible, 2 is the preferred option. If it's being used a few places here and there, consider the simple approach of 1.