Using A 3.5 Assembly In SSIS 2008

The following is based on recent experience when I needed to call a web service to send out emails from Integration Services 2008 R2.

You probably already know that there are two components in SSIS that allow you to write custom .NET code.  In the Control Flow we’ve got the Script Task, while the Data Flow offers us the Script Component.

In my case I wanted to encapsulate the code that uses the external web service into a custom .NET DLL, aka assembly.  The code to send out emails is needed in several SSIS packages, so to be able to handle changes easily and to promote reuse, this really needed to get encapsulated into one library.

Furthermore, the library is configuring the binding with the web service completely through code so that no app.config file is needed.  The assembly is developed using Visual Studio 2008 and needs the 3.5 .NET Framework.

Assuming that the assembly is already developed and fully tested, open up a package in the BIDS and add a Script Task to the Control Flow.  Open up the Visual Studio Tools for Applications 2.0 (aka VSTA) development environment by clicking the Edit Script… button in the Script Task Editor.

Right-click the References node in the Project Explorer and select Add Reference….

Adding a reference to the Script Task

In the Add Reference popup window, select the Browse tab and select your custom assembly.  After clicking OK, you’re getting the following warning:

Warning when adding a custom assembly to a Script Task in Integration Services

It says that your assembly, or one of its dependencies, requires a later version of the .NET Framework than the one specified in the project.  Click the No button so that we can first set the target framework to the expected version.

To do that, right-click the project node in the Project Explorer and select Properties.  In the Application page, you can see that Target Framework is set to .NET Framework 2.0.

By default, the Target Framework for the Script Task in SSIS 2008 is set to .NET Framework 2.0

Apparently by default the Script Task in Integration Services 2008 R2 still targets the 2.0 Framework.  Change that setting to .NET Framework 3.5.  You’ll get asked if it’s okay to close and reopen the current project, which is needed to change the Target Framework.  That’s okay, so click the Yes button.

Target Framework Change: requires a close/reopen of the current project

With this setting modified you can now add the reference to your custom assembly and start using it in the code.

Tip: in case that the reference to your assembly keeps disappearing every time you reopen the Script Task’s code, click the Save All button – that’s the one that looks like a bunch of blue floppies – in the toolbar after you’ve added the reference.  Or hit CTRL + SHIFT + S.

Besides the scenario described above, a second reason when you may want (or better, have) to change the target framework version for your Script Task or Component is when you actually need to use functionality that doesn’t exist yet in 2.0.  New functionality in .NET 3.5, such as Linq, can only be used if the target framework is switched to 3.5.

That’s it for now, have fun!

Valentino.

References

Where is my app.config for SSIS? by Darren Green

Share

Tags: ,

  1. Darren’s avatar

    Hi there,

    This is pretty cool and just what I need…almost! Except our conversion from SSIS 2005 means all our tasks are VB.NET Scripts and the properties option does not appear in the script project explorer when the script is a VB.NET script. You can right click the scripcomponent top node and get the properties but the Application tab does not have any option for changing the default target framework. I do not have time to convert all of our scripts to C# (we have 46 packages each with lots of script tasks!). Any idea why it is different in VB.NET and how we can do it? I have tried everything!

    Thanks
    Darren

    Reply

  2. Darren’s avatar

    OK – found the way of doing it in VB.NET thanks to:

    http://www.demiliani.com/blog/archive/2007/08/24/6430.aspx

    as the man says in his blog – ridiculous that its so different!

    Reply

    1. Valentino Vranken’s avatar

      Hi Darren,

      I’m glad you’ve found the solution to your problem! As I’m not a VB.NET user, I would have resorted to a web search as well :-)

      It’s indeed ridiculous that C# and VB.NET differ so much, but as these two products are being developed by different teams, I guess it’s somehow understandable. At least the setting was also located under the Project settings, though hidden a little deeper…

      Best regards,
      Valentino.

      Reply

© 2008-2018 BI: Beer Intelligence? All Rights Reserved